20190325215156_update_status_reply_count.exs (1409B)
1 defmodule Pleroma.Repo.Migrations.UpdateStatusReplyCount do
2 use Ecto.Migration
3
4 @public "https://www.w3.org/ns/activitystreams#Public"
5
6 def up do
7 execute("""
8 WITH reply_count AS (
9 SELECT count(*) AS count, data->>'inReplyTo' AS ap_id
10 FROM objects
11 WHERE
12 data->>'inReplyTo' IS NOT NULL AND
13 data->>'type' = 'Note' AND (
14 data->'cc' ? '#{@public}' OR
15 data->'to' ? '#{@public}')
16 GROUP BY data->>'inReplyTo'
17 )
18 UPDATE objects AS o
19 SET "data" = jsonb_set(o.data, '{repliesCount}', reply_count.count::varchar::jsonb, true)
20 FROM reply_count
21 WHERE reply_count.ap_id = o.data->>'id';
22 """)
23
24 execute("""
25 WITH reply_count AS (SELECT
26 count(*) as count,
27 data->'object'->>'inReplyTo' AS ap_id
28 FROM
29 activities
30 WHERE
31 data->'object'->>'inReplyTo' IS NOT NULL AND
32 data->'object'->>'type' = 'Note' AND (
33 data->'object'->'cc' ? '#{@public}' OR
34 data->'object'->'to' ? '#{@public}')
35 GROUP BY
36 data->'object'->>'inReplyTo'
37 )
38 UPDATE activities AS a
39 SET "data" = jsonb_set(a.data, '{object, repliesCount}', reply_count.count::varchar::jsonb, true)
40 FROM reply_count
41 WHERE reply_count.ap_id = a.data->'object'->>'id';
42 """)
43 end
44
45 def down do
46 :noop
47 end
48 end