1 defmodule Pleroma.Repo.Migrations.UpdateUserNoteCounters do
4 @public "https://www.w3.org/ns/activitystreams#Public"
8 WITH public_note_count AS (
10 data->>'actor' AS actor,
13 WHERE data->>'type' = 'Note' AND (
14 data->'cc' ? '#{@public}' OR data->'to' ? '#{@public}'
16 GROUP BY data->>'actor'
19 SET "info" = jsonb_set(u.info, '{note_count}', o.count::varchar::jsonb, true)
20 FROM public_note_count AS o
21 WHERE u.ap_id = o.actor
27 WITH public_note_count AS (
29 data->>'actor' AS actor,
32 WHERE data->>'type' = 'Note'
33 GROUP BY data->>'actor'
36 SET "info" = jsonb_set(u.info, '{note_count}', o.count::varchar::jsonb, true)
37 FROM public_note_count AS o
38 WHERE u.ap_id = o.actor