1,035
edits
Filter Duplicate Boosts: Difference between revisions
no edit summary
No edit summary |
No edit summary |
||
| (2 intermediate revisions by the same user not shown) | |||
| Line 5: | Line 5: | ||
|Contributors=Jonny | |Contributors=Jonny | ||
|Has Git Repository=https://github.com/NeuromatchAcademy/mastodon | |Has Git Repository=https://github.com/NeuromatchAcademy/mastodon | ||
|Completion Status= | |Completion Status=Completed | ||
|Active Status=Inactive | |Active Status=Inactive | ||
}} | }} | ||
* Pull Request: [[Has Pull Request::https://github.com/NeuromatchAcademy/mastodon/pull/36]] | * Pull Request: [[Has Pull Request::https://github.com/NeuromatchAcademy/mastodon/pull/36]] | ||
* Bugfix - respect local vs remote scope: [[Has Pull Request::https://github.com/NeuromatchAcademy/mastodon/pull/38]] | |||
== Problem == | == Problem == | ||
| Line 86: | Line 87: | ||
== 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 max_boost_id_scope | |||
Status.where(<<~SQL.squish) | |||
"statuses"."id" = ( | |||
SELECT MAX(id) | |||
FROM "statuses" "s2" | |||
WHERE "s2"."reblog_of_id" = "statuses"."reblog_of_id" | |||
#{'AND ("s2"."local" = true OR "s2"."uri" IS NULL)' if local_only?} | |||
#{'AND "s2"."local" = false AND "s2"."uri" IS NOT NULL' if remote_only?} | |||
) | |||
SQL | |||
end | |||
def without_duplicate_reblogs | |||
Status.where(statuses: { reblog_of_id: nil }) | |||
.or(max_boost_id_scope) | |||
end | |||
</syntaxhighlight> | |||
==== Caveats ==== | |||
Note that we are using string interpolations of the <code>Status.local</code> and <code>Status.remote</code> scopes because there isn't a way, as far as [[Jonny]] can tell, to change the table alias of a scope. This shouldn't be that big of an issue (if, eg. the definition of those scopes changes) because the tests should catch most cases. | |||
==== 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 ("s2"."local" = true OR "s2"."uri" IS NULL) | |||
)) | |||
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 254: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
=== Example Query === | ==== Example Query ==== | ||
<syntaxhighlight lang="sql"> | <syntaxhighlight lang="sql"> | ||