Skip to content

Update AbuseReportFinder to allow filtering by reporter

Eugie Limpin requested to merge el-abuse-reports-finder-filter-by-reporter into master

What does this MR do and why?

Add AbuseReport.by_reporter_id scope that is used in AbuseReportsFinder to allow filtering reports by reporter_id.

Database changes

AbuseReport.by_reporter_id scope

Query plans were generated after inserting 20k abuse report records.

INSERT statements 
" 10k abuse report records: reporter_id=9670031, status = 1 (open)
"
INSERT INTO "abuse_reports" ("reporter_id", "user_id", "message", "created_at", "updated_at", "message_html", "cached_markdown_version")
SELECT 9670031, user_id, 'spammer', '2023-02-07 09:51:12.678028', '2023-02-07 09:51:12.678028', 'spammer', 2097152 FROM generate_series(4018077, 4028077) as user_id;

" 10k abuse report records: reporter_id=2709171, status = 2 (closed)
"
INSERT INTO "abuse_reports" ("reporter_id", "user_id", "message", "created_at", "updated_at", "message_html", "cached_markdown_version", "category", "status")
SELECT 2709171, user_id, 'spammer', '2023-02-07 09:51:12.678028', '2023-02-07 09:51:12.678028', 'spammer', 2097152, 2, 2 FROM generate_series(4028078, 4038078) as user_id;

Filtering reports by reporter_id

Index on (reporter_id, id) was removed to lessen the number of introduced indexes as suggested. Removal should be okay since the list is filtered by status (open by default) if the user doesn't add a status filter so (status, reporter_id, id) index should cover the most likely query pattern.

Filtering reports by reporter_id and status

Covered by the index created with CREATE INDEX index_abuse_reports_on_status_reporter_id_and_id ON abuse_reports USING btree (status, reporter_id, id);.

Raw SQL 
EXPLAIN
SELECT
    "abuse_reports".*
FROM
    "abuse_reports"
WHERE
    "abuse_reports"."reporter_id" = 2709171
    AND "abuse_reports"."status" = 2
ORDER BY
    "abuse_reports"."id" DESC
LIMIT 20 OFFSET 9980
Query plan https://console.postgres.ai/shared/e8c019d3-1df2-4845-86f2-f403fd971b3d

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Eugie Limpin

Merge request reports