4
Fixing per-user post and comment scores
(derp.foo)
Sad that updating your Lemmy instance to 0.18.1 broke your post and comment scores? Here's a small SQL script to fix them:
MERGE INTO "person_aggregates" AS "d"
USING (SELECT "m"."id" AS "person_id"
, coalesce("p"."post_count", 0) AS "post_count"
, coalesce("p"."post_score", 0) AS "post_score"
, coalesce("c"."comment_count", 0) AS "comment_count"
, coalesce("c"."comment_score", 0) AS "comment_score"
FROM "person" AS "m"
LEFT JOIN (SELECT "p"."creator_id"
, count(distinct "p"."id") AS "post_count"
, sum("l"."score") AS "post_score"
FROM "post" AS "p"
LEFT JOIN "post_like" AS "l"
ON "l"."post_id" = "p"."id"
WHERE NOT "p"."removed"
AND NOT "p"."deleted"
AND "l"."person_id" <> "p"."creator_id"
GROUP BY "p"."creator_id") AS "p"
ON "p"."creator_id" = "m"."id"
LEFT JOIN (SELECT "c"."creator_id"
, count(distinct "c"."id") AS "comment_count"
, sum("l"."score") AS "comment_score"
FROM "comment" AS "c"
LEFT JOIN "comment_like" AS "l"
ON "l"."comment_id" = "c"."id"
WHERE NOT "c"."removed"
AND NOT "c"."deleted"
AND "l"."person_id" <> "c"."creator_id"
GROUP BY "c"."creator_id") AS "c"
ON "c"."creator_id" = "m"."id"
ORDER BY "m"."id") AS "s"
ON "s"."person_id" = "d"."person_id"
WHEN MATCHED AND ("d"."post_count" <> "s"."post_count" OR
"d"."post_score" <> "s"."post_score" OR
"d"."comment_count" <> "s"."comment_count" OR
"d"."comment_score" <> "s"."comment_score")
THEN UPDATE
SET "post_count" = "s"."post_count"
, "post_score" = "s"."post_score"
, "comment_count" = "s"."comment_count"
, "comment_score" = "s"."comment_score";
As with all scripts that work directly on the PostgreSQL database: warranty void when connected, your kilometrage may vary, do not look into laser with remaining eye, etc.
Edit: don't count self-votes.