1,035
edits
Filter Duplicate Boosts: Difference between revisions
no edit summary
No edit summary |
No edit summary |
||
| Line 86: | Line 86: | ||
== Implementation == | == Implementation == | ||
=== Simpler Implementation (good) === | |||
Instead of doing all that garbage below, we can just restate the problem as "we want posts that aren't boosts, or posts that are boosts with the maximum ID for all boosts of a given post" | |||
Like this: | |||
<syntaxhighlight lang="ruby"> | |||
def without_duplicate_reblogs | |||
Status.where(statuses: { reblog_of_id: nil }) | |||
.or(Status.where(<<~SQL.squish)) | |||
"statuses"."id" = ( | |||
SELECT MAX(id) | |||
FROM statuses s2 | |||
WHERE s2.reblog_of_id = statuses.reblog_of_id | |||
) | |||
SQL | |||
end | |||
</syntaxhighlight> | |||
==== Example Query ==== | |||
<syntaxhighlight lang="sql"> | |||
SELECT "statuses"."id", | |||
"statuses"."updated_at" | |||
FROM "statuses" | |||
INNER JOIN "accounts" ON "accounts"."id" = "statuses"."account_id" | |||
WHERE "statuses"."visibility" = $1 | |||
AND "accounts"."suspended_at" IS NULL | |||
AND "accounts"."silenced_at" IS NULL | |||
AND (statuses.reply = FALSE | |||
OR statuses.in_reply_to_account_id = statuses.account_id) | |||
AND ("statuses"."reblog_of_id" IS NULL | |||
OR "statuses"."id" = | |||
(SELECT MAX(id) | |||
FROM statuses s2 | |||
WHERE s2.reblog_of_id = statuses.reblog_of_id)) | |||
AND ("statuses"."local" = $2 | |||
OR "statuses"."uri" IS NULL) | |||
AND "statuses"."deleted_at" IS NULL | |||
AND 1=1 | |||
AND "statuses"."id" < 111813463418866657 | |||
ORDER BY "statuses"."id" DESC LIMIT $3 [["visibility", 0], ["local", true], ["LIMIT", 20]] | |||
</syntaxhighlight> | |||
=== Original Implementation (bad) === | |||
Actually pretty damn simple. Add an additional scope in <code>public_feed.rb</code> | Actually pretty damn simple. Add an additional scope in <code>public_feed.rb</code> | ||
| Line 195: | Line 241: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
=== Example Query === | ==== Example Query ==== | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||