Mise à jour des Likes dans `user_stats` dans la base de données via SQL

Soutien Installation
1

Bonjour à tous !

Existe-t-il un endroit où je pourrais trouver une requête préfabriquée pour mettre à jour likes_given et likes_received dans la table user_stats ? Je ne suis pas sûr de la provenance de cette requête, probablement quelque chose basé sur ce qui se trouve dans les tables user_actions et posts ?

Cela concerne une migration où j’utilise déjà quelques requêtes que j’ai récupérées chez l’un des importateurs, je pense :

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

Cela met à jour les comptes de « J’aime » dans posts et topics, mais pas dans users_stats…

2

You’ll need to do it in rails or a plugin. Data explorer can’t change data.

You’ll need to have a look at the code in the user or user stats models.

3 « J'aime »
3

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 :slightly_frowning_face:

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
1 « J'aime »