0

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?

ruslanway
  • 285
  • 2
  • 8

1 Answers1

1

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
Zach J.
  • 366
  • 6
  • It looks great. But it works only "by demand" right? I mean it's not automatically populate num_likes when the Like entry creates? – ruslanway Nov 23 '22 at 17:19
  • I don't know how your ETL process works for your database - if you have control over the code that inserts the new Like entries, you could just add this piece to it. – Zach J. Nov 23 '22 at 17:29
  • 1
    Yes, actually I use it in my Django project, so I can do it with Django-ORM. I think in that case I can just add the population act of num_likes field of Post to .save() method of the Like model. Or just increment it by 1. – ruslanway Nov 23 '22 at 17:37
  • @ruslanway sounds like a good strategy! – Zach J. Nov 23 '22 at 17:47