diff options
author | Eugen Rochko <eugen@zeonfederated.com> | 2021-04-12 12:37:14 +0200 |
---|---|---|
committer | GitHub <noreply@github.com> | 2021-04-12 12:37:14 +0200 |
commit | f7117646afddb2676e9275d8efe90c3a20c59021 (patch) | |
tree | efb9ba8f7b22d27b0a1ea595cfa30030f5d03b09 /db/schema.rb | |
parent | ad61265268f13d9b2a04e2e176724d8a7376f85a (diff) |
Add cold-start follow recommendations (#15945)
Diffstat (limited to 'db/schema.rb')
-rw-r--r-- | db/schema.rb | 63 |
1 files changed, 57 insertions, 6 deletions
diff --git a/db/schema.rb b/db/schema.rb index 4edaf5651..28f36abb1 100644 --- a/db/schema.rb +++ b/db/schema.rb @@ -2,15 +2,15 @@ # of editing this file, please use the migrations feature of Active Record to # incrementally modify your database, and then regenerate this schema definition. # -# Note that this schema.rb definition is the authoritative source for your -# database schema. If you need to create the application database on another -# system, you should be using db:schema:load, not running all the migrations -# from scratch. The latter is a flawed and unsustainable approach (the more migrations -# you'll amass, the slower it'll run and the greater likelihood for issues). +# This file is the source Rails uses to define your schema when running `bin/rails +# db:schema:load`. When creating a new database, `bin/rails db:schema:load` tends to +# be faster and is potentially less error prone than running all of your +# migrations from scratch. Old migrations may fail to apply correctly if those +# migrations use external dependencies or application code. # # It's strongly recommended that you check this file into your version control system. -ActiveRecord::Schema.define(version: 2021_03_08_133107) do +ActiveRecord::Schema.define(version: 2021_03_24_171613) do # These are extensions that must be enabled in order to support this database enable_extension "plpgsql" @@ -406,6 +406,13 @@ ActiveRecord::Schema.define(version: 2021_03_08_133107) do t.index ["tag_id"], name: "index_featured_tags_on_tag_id" end + create_table "follow_recommendation_suppressions", force: :cascade do |t| + t.bigint "account_id", null: false + t.datetime "created_at", precision: 6, null: false + t.datetime "updated_at", precision: 6, null: false + t.index ["account_id"], name: "index_follow_recommendation_suppressions_on_account_id", unique: true + end + create_table "follow_requests", force: :cascade do |t| t.datetime "created_at", null: false t.datetime "updated_at", null: false @@ -996,6 +1003,7 @@ ActiveRecord::Schema.define(version: 2021_03_08_133107) do add_foreign_key "favourites", "statuses", name: "fk_b0e856845e", on_delete: :cascade add_foreign_key "featured_tags", "accounts", on_delete: :cascade add_foreign_key "featured_tags", "tags", on_delete: :cascade + add_foreign_key "follow_recommendation_suppressions", "accounts", on_delete: :cascade add_foreign_key "follow_requests", "accounts", column: "target_account_id", name: "fk_9291ec025d", on_delete: :cascade add_foreign_key "follow_requests", "accounts", name: "fk_76d644b0e7", on_delete: :cascade add_foreign_key "follows", "accounts", column: "target_account_id", name: "fk_745ca29eac", on_delete: :cascade @@ -1079,4 +1087,47 @@ ActiveRecord::Schema.define(version: 2021_03_08_133107) do SQL add_index "instances", ["domain"], name: "index_instances_on_domain", unique: true + create_view "account_summaries", materialized: true, sql_definition: <<-SQL + SELECT accounts.id AS account_id, + mode() WITHIN GROUP (ORDER BY t0.language) AS language, + mode() WITHIN GROUP (ORDER BY t0.sensitive) 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 = true) AND (accounts.locked = false)) + GROUP BY accounts.id; + SQL + add_index "account_summaries", ["account_id"], name: "index_account_summaries_on_account_id", unique: true + + create_view "follow_recommendations", sql_definition: <<-SQL + SELECT t0.account_id, + sum(t0.rank) AS rank, + array_agg(t0.reason) AS reason + FROM ( SELECT accounts.id AS account_id, + ((count(follows.id))::numeric / (1.0 + (count(follows.id))::numeric)) AS rank, + 'most_followed'::text AS reason + FROM ((follows + JOIN accounts ON ((accounts.id = follows.target_account_id))) + JOIN users ON ((users.account_id = follows.account_id))) + WHERE ((users.current_sign_in_at >= (now() - 'P30D'::interval)) AND (accounts.suspended_at IS NULL) AND (accounts.moved_to_account_id IS NULL) AND (accounts.silenced_at IS NULL) AND (accounts.locked = false) AND (accounts.discoverable = true)) + GROUP BY accounts.id + HAVING (count(follows.id) >= 5) + UNION ALL + SELECT accounts.id AS account_id, + (sum((status_stats.reblogs_count + status_stats.favourites_count)) / (1.0 + sum((status_stats.reblogs_count + status_stats.favourites_count)))) AS rank, + 'most_interactions'::text AS reason + FROM ((status_stats + JOIN statuses ON ((statuses.id = status_stats.status_id))) + JOIN accounts ON ((accounts.id = statuses.account_id))) + WHERE ((statuses.id >= (((date_part('epoch'::text, (now() - 'P30D'::interval)) * (1000)::double precision))::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 = false) AND (accounts.discoverable = true)) + GROUP BY accounts.id + HAVING (sum((status_stats.reblogs_count + status_stats.favourites_count)) >= (5)::numeric)) t0 + GROUP BY t0.account_id + ORDER BY (sum(t0.rank)) DESC; + SQL end |