diff options
author | Claire <claire.github-309c@sitedethib.com> | 2021-04-20 12:17:14 +0200 |
---|---|---|
committer | Claire <claire.github-309c@sitedethib.com> | 2021-04-20 12:17:14 +0200 |
commit | e2a2bc90213a653b772b457499cedbfe2e830d74 (patch) | |
tree | c97643e3977ce9110fdf081ed3f3a70ae1a4457f /db/views | |
parent | df326b8b5c0659edb2aca77690a892f228b0e099 (diff) | |
parent | b5ac17c4b6bfa85494fd768bbf1af87ca79b622b (diff) |
Merge branch 'main' into glitch-soc/merge-upstream
Conflicts: - `README.md`: Upstream updated copyright year, we don't mention it so kept our version. - `app/controllers/admin/dashboard_controller.rb`: Not really a conflict, upstream change (removing the spam checker) too close to glitch-soc changes. Ported upstream changes. - `app/models/form/admin_settings.rb`: Same. - `app/services/remove_status_service.rb`: Same. - `app/views/admin/settings/edit.html.haml`: Same. - `config/settings.yml`: Same. - `config/environments/production.rb`: Not a real conflict, upstream added a default HTTP header, but we have extra headers in glitch-soc. Added the header.
Diffstat (limited to 'db/views')
-rw-r--r-- | db/views/account_summaries_v01.sql | 22 | ||||
-rw-r--r-- | db/views/follow_recommendations_v01.sql | 38 |
2 files changed, 60 insertions, 0 deletions
diff --git a/db/views/account_summaries_v01.sql b/db/views/account_summaries_v01.sql new file mode 100644 index 000000000..5a632b622 --- /dev/null +++ b/db/views/account_summaries_v01.sql @@ -0,0 +1,22 @@ +SELECT + accounts.id AS account_id, + mode() WITHIN GROUP (ORDER BY language ASC) AS language, + mode() WITHIN GROUP (ORDER BY sensitive ASC) AS sensitive +FROM accounts +CROSS JOIN LATERAL ( + SELECT + statuses.account_id, + statuses.language, + statuses.sensitive + FROM statuses + WHERE statuses.account_id = accounts.id + AND statuses.deleted_at IS NULL + ORDER BY statuses.id DESC + LIMIT 20 +) t0 +WHERE accounts.suspended_at IS NULL + AND accounts.silenced_at IS NULL + AND accounts.moved_to_account_id IS NULL + AND accounts.discoverable = 't' + AND accounts.locked = 'f' +GROUP BY accounts.id diff --git a/db/views/follow_recommendations_v01.sql b/db/views/follow_recommendations_v01.sql new file mode 100644 index 000000000..799abeaee --- /dev/null +++ b/db/views/follow_recommendations_v01.sql @@ -0,0 +1,38 @@ +SELECT + account_id, + sum(rank) AS rank, + array_agg(reason) AS reason +FROM ( + SELECT + accounts.id AS account_id, + count(follows.id) / (1.0 + count(follows.id)) AS rank, + 'most_followed' AS reason + FROM follows + INNER JOIN accounts ON accounts.id = follows.target_account_id + INNER JOIN users ON users.account_id = follows.account_id + WHERE users.current_sign_in_at >= (now() - interval '30 days') + AND accounts.suspended_at IS NULL + AND accounts.moved_to_account_id IS NULL + AND accounts.silenced_at IS NULL + AND accounts.locked = 'f' + AND accounts.discoverable = 't' + GROUP BY accounts.id + HAVING count(follows.id) >= 5 + UNION ALL + SELECT accounts.id AS account_id, + sum(reblogs_count + favourites_count) / (1.0 + sum(reblogs_count + favourites_count)) AS rank, + 'most_interactions' AS reason + FROM status_stats + INNER JOIN statuses ON statuses.id = status_stats.status_id + INNER JOIN accounts ON accounts.id = statuses.account_id + WHERE statuses.id >= ((date_part('epoch', now() - interval '30 days') * 1000)::bigint << 16) + AND accounts.suspended_at IS NULL + AND accounts.moved_to_account_id IS NULL + AND accounts.silenced_at IS NULL + AND accounts.locked = 'f' + AND accounts.discoverable = 't' + GROUP BY accounts.id + HAVING sum(reblogs_count + favourites_count) >= 5 +) t0 +GROUP BY account_id +ORDER BY rank DESC |