[bugfix] Account timeline: exclude self-replies that mention other accounts (#2670)

* Account timeline: exclude self-replies that mention other accounts

* Add index for querying unmentioned statuses

* remove now unused statuses_account_id_id_idx

---------

Co-authored-by: tobi <tobi.smethurst@protonmail.com>

1 files changed, 20 insertions, 0 deletions

diff --git a/internal/db/bundb/util.go b/internal/db/bundb/util.go
index e2dd392dc..c7a2b96c2 100644
--- a/internal/db/bundb/util.go
+++ b/internal/db/bundb/util.go
@@ -206,3 +206,23 @@ func parseWhere(w db.Where) (query string, args []interface{}) {
 	args = []interface{}{bun.Ident(w.Key), w.Value}
 	return
 }
+
+// whereArrayIsNullOrEmpty extends a query with a where clause requiring an array to be null or empty.
+// (The empty check varies by dialect; only PG has direct support for SQL array types.)
+func whereArrayIsNullOrEmpty(query *bun.SelectQuery, subject interface{}) *bun.SelectQuery {
+	var arrayEmptySQL string
+	switch d := query.Dialect().Name(); d {
+	case dialect.SQLite:
+		arrayEmptySQL = "json_array_length(?) = 0"
+	case dialect.PG:
+		arrayEmptySQL = "CARDINALITY(?) = 0"
+	default:
+		log.Panicf(nil, "db conn %s was neither pg nor sqlite", d)
+	}
+
+	return query.WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery {
+		return q.
+			Where("? IS NULL", subject).
+			WhereOr(arrayEmptySQL, subject)
+	})
+}