grain.social is a photo sharing platform built on atproto.
grain.social
atproto
photography
appview
1import type { BaseContext } from "$hatk";
2
3/** SQL condition that excludes orphaned replies (parent comment was deleted). */
4export const NOT_ORPHANED = `(c.reply_to IS NULL OR EXISTS (
5 SELECT 1 FROM "social.grain.comment" p WHERE p.uri = c.reply_to
6))`;
7
8/** Count non-orphaned comments grouped by subject URI. */
9export async function countComments(
10 db: BaseContext["db"],
11 subjectUris: string[],
12): Promise<Map<string, number>> {
13 if (subjectUris.length === 0) return new Map();
14 const placeholders = subjectUris.map((_, i) => `$${i + 1}`).join(",");
15 const rows = (await db.query(
16 `SELECT c.subject, COUNT(*) as count FROM "social.grain.comment" c
17 WHERE c.subject IN (${placeholders}) AND ${NOT_ORPHANED}
18 GROUP BY c.subject`,
19 subjectUris,
20 )) as { subject: string; count: number }[];
21 const m = new Map<string, number>();
22 for (const r of rows) m.set(r.subject, Number(r.count));
23 return m;
24}