Is there somewhere where I can find a ready-made query to update likes_given and likes_received in the user_stats table? I’m not sure where that is supposed to come from, probably something based on what is in the user_actions and posts table?
This is for a migration where I’m already using a couple of queries I grabbed from one of the importers, I think:
UPDATE posts SET like_count = coalesce(cnt,0)
FROM (
SELECT post_id, count(*) cnt
FROM post_actions
WHERE post_action_type_id = 2 AND deleted_at IS NULL
GROUP BY post_id
) x
WHERE posts.like_count <> x.cnt AND posts.id = x.post_id
UPDATE topics SET like_count = coalesce(cnt,0)
FROM (
SELECT topic_id, sum(like_count) cnt
FROM posts
WHERE deleted_at IS NULL
GROUP BY topic_id
) x
WHERE topics.like_count <> x.cnt AND topics.id = x.topic_id
That updates the Like counts in posts and topics, but not in users_stats…
I’m using DBeaver, I can use UPDATE queries. I assumed “Data explorer” was just another equivalent DB client, sorry for my confusion. I guess this doesn’t belong here then, if an admin wants to split these posts into a separate thread, that’s ok. I suggest a title like Updating likes in user_stats via SQL.
I came up with the following, but I am not sure if they are correct, Id love to check by finding out some place where this is already made by someone more knowledgeable, but I can’t find it
UPDATE user_stats SET likes_given = coalesce(cnt,0)
FROM (
SELECT user_id, count(*) cnt
FROM post_actions
WHERE post_action_type_id = 2 AND deleted_at IS NULL
GROUP BY user_id
) x
WHERE user_stats.likes_given <> x.cnt
AND user_stats.user_id = x.user_id
UPDATE user_stats SET likes_received = coalesce(cnt,0)
FROM (
SELECT posts.user_id, SUM(posts.like_count) cnt
FROM post_actions
LEFT JOIN posts ON post_actions.post_id = posts.id
WHERE post_action_type_id = 2 AND post_actions.deleted_at IS NULL
GROUP BY posts.user_id
) x
WHERE user_stats.likes_received <> x.cnt
AND user_stats.user_id = x.user_id