How to store backward ForeignKey relational COUNT in a table?-CodePudding

I have a table Post(id,date,title,content,author)
and a table Like(id,date,author,post->ForeignKey(Post))
So I want to add a field 'likes' to the table Post to store the COUNT of likes to have a convenient way to get the COUNT of likes that each post-object has.
Is it possible to implement?

CodePudding user response：

first, add the column in the post table:

ALTER TABLE Post
ADD num_likes int;

Then update the post table using the likes table (SO question for ref):

with likes as {
  Select post, Count(1) as num_likes
  FROM Like
  GROUP BY post
}

UPDATE Post 
SET Post.num_likes = likes.num_likes 
FROM Post INNER JOIN likes ON Post.post = likes.post