1 # Pleroma: A lightweight social networking server
2 # Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
3 # SPDX-License-Identifier: AGPL-3.0-only
5 defmodule Pleroma.Repo.Migrations.UpdateUserNoteCounters do
8 @public "https://www.w3.org/ns/activitystreams#Public"
12 WITH public_note_count AS (
14 data->>'actor' AS actor,
17 WHERE data->>'type' = 'Note' AND (
18 data->'cc' ? '#{@public}' OR data->'to' ? '#{@public}'
20 GROUP BY data->>'actor'
23 SET "info" = jsonb_set(u.info, '{note_count}', o.count::varchar::jsonb, true)
24 FROM public_note_count AS o
25 WHERE u.ap_id = o.actor
31 WITH public_note_count AS (
33 data->>'actor' AS actor,
36 WHERE data->>'type' = 'Note'
37 GROUP BY data->>'actor'
40 SET "info" = jsonb_set(u.info, '{note_count}', o.count::varchar::jsonb, true)
41 FROM public_note_count AS o
42 WHERE u.ap_id = o.actor