10603810623

Committed 28 Aug 2024 08:19PM UTC coverage: 32.008% (-0.05%) from 32.058%

Build # 10603810623

Build Type

Pull #9654

github

Committed by

Raemon

Commit Message

deleted draft

Pull Request Pull Request #9654: Auto Spotlights for Best of LessWrong

Run Details

5020 of 40841 branches covered (12.29%)

Branch coverage included in aggregate %.

6 of 44 new or added lines in 2 files covered. (13.64%)

382 existing lines in 10 files now uncovered.

29228 of 66158 relevant lines covered (44.18%)

189.3 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

18.99

/packages/lesswrong/server/repos/CommentsRepo.ts

import Comments from "../../lib/collections/comments/collection";
import AbstractRepo from "./AbstractRepo";
import SelectQuery from "@/server/sql/SelectQuery";
import keyBy from 'lodash/keyBy';
import groupBy from 'lodash/groupBy';
import orderBy from 'lodash/orderBy';
import { filterWhereFieldsNotNull } from "../../lib/utils/typeGuardUtils";
import { EA_FORUM_COMMUNITY_TOPIC_ID } from "../../lib/collections/tags/collection";
import { recordPerfMetrics } from "./perfMetricWrapper";
import { forumSelect } from "../../lib/forumTypeUtils";
import { isAF } from "../../lib/instanceSettings";
import { getViewablePostsSelector } from "./helpers";

type ExtendedCommentWithReactions = DbComment & {
  yourVote?: string,
  theirVote?: string,
  userVote?: string,
}

class CommentsRepo extends AbstractRepo<"Comments"> {
  constructor() {
    super(Comments);
  }

  async getPromotedCommentsOnPosts(postIds: string[]): Promise<(DbComment|null)[]> {
    const rawComments = await this.manyOrNone(`
      -- CommentsRepo.getPromotedCommentsOnPosts
      SELECT c.*
      FROM "Comments" c
      JOIN (
          SELECT "postId", MAX("promotedAt") AS max_promotedAt
          FROM "Comments"
          WHERE "postId" IN ($1:csv)
          AND "promotedAt" IS NOT NULL
          GROUP BY "postId"
      ) sq
      ON c."postId" = sq."postId" AND c."promotedAt" = sq.max_promotedAt;
    `, [postIds]);
    
    const comments = filterWhereFieldsNotNull(rawComments, "postId");
    const commentsByPost = keyBy(comments, c=>c.postId);
    return postIds.map(postId => commentsByPost[postId] ?? null);
  }

  async getRecentCommentsOnPosts(postIds: string[], limit: number, filter: MongoSelector<DbComment>): Promise<DbComment[][]> {
    const selectQuery = new SelectQuery(this.getCollection().getTable(), filter)
    const selectQueryAtoms = selectQuery.compileSelector(filter);
    const {sql: filterWhereClause, args: filterArgs} = selectQuery.compileAtoms(selectQueryAtoms, 2);

    const comments = await this.manyOrNone(`
      -- CommentsRepo.getRecentCommentsOnPosts
      WITH cte AS (
        SELECT
          comment_with_rownumber.*,
          ROW_NUMBER() OVER (PARTITION BY comment_with_rownumber."postId" ORDER BY comment_with_rownumber."postedAt" DESC) as rn
        FROM "Comments" comment_with_rownumber
        WHERE comment_with_rownumber."postId" IN ($1:csv)
        AND (
          ${filterWhereClause}
        )
      )
      SELECT *
      FROM cte
      WHERE rn <= $2
    `, [postIds, limit, ...filterArgs]);
    
    const commentsByPost = groupBy(comments, c=>c.postId);
    return postIds.map(postId =>
      orderBy(
        commentsByPost[postId] ?? [],
        c => -c.postedAt.getTime()
      )
    );
  }
  
  async getCommentsWithReacts(limit: number): Promise<(DbComment|null)[]> {
    return await this.manyOrNone(`
      -- CommentsRepo.getCommentsWithReacts
      SELECT c.*
      FROM "Comments" c
      JOIN (
          SELECT "documentId", MIN("votedAt") AS most_recent_react
          FROM "Votes"
          WHERE "collectionName" = 'Comments' AND "extendedVoteType"->'reacts' != '[]'::jsonb
          GROUP BY "documentId"
          ORDER BY most_recent_react DESC
          LIMIT $1
      ) v
      ON c._id = v."documentId"
      ORDER BY v.most_recent_react DESC;
    `, [limit]);
  }

  async getPopularPollComments (limit: number, pollCommentId: string): Promise<(ExtendedCommentWithReactions)[]> {
    return await this.getRawDb().manyOrNone(`
      -- CommentsRepo.getPopularPollComments
      SELECT c.*
      FROM public."Comments" AS c
      WHERE c."parentCommentId" = $2
      ORDER BY c."baseScore" DESC
      LIMIT $1
    `, [limit, pollCommentId]);
  }

  async getPopularPollCommentsWithUserVotes (userId: string, limit: number, pollCommentId: string): Promise<(ExtendedCommentWithReactions)[]> {
    return await this.getRawDb().manyOrNone(`
    -- CommentsRepo.getPopularPollCommentsWithUserVotes
    SELECT c.*, v."extendedVoteType"->'reacts'->0->>'react' AS "yourVote"
    FROM public."Comments" AS c
    INNER JOIN public."Votes" AS v ON c._id = v."documentId"
    WHERE
      c."parentCommentId" = $3
      AND v."userId" = $1
      AND v."extendedVoteType"->'reacts'->0->>'vote' = 'created'
      AND v.cancelled IS NOT TRUE
      AND v."isUnvote" IS NOT TRUE
    ORDER BY c."baseScore" DESC
    LIMIT $2
    `, [userId, limit, pollCommentId]);
  }

  async getPopularPollCommentsWithTwoUserVotes (userId: string, targetUserId: string, limit: number, pollCommentId: string): Promise<(ExtendedCommentWithReactions)[]> {
    return await this.getRawDb().manyOrNone(`
    -- CommentsRepo.getPopularPollCommentsWithTwoUserVotes
    SELECT c.*, 
        v1."extendedVoteType"->'reacts'->0->>'react' AS "yourVote", 
        v2."extendedVoteType"->'reacts'->0->>'react' AS "theirVote"
    FROM public."Comments" AS c
    LEFT JOIN public."Votes" AS v1 ON c._id = v1."documentId"
    LEFT JOIN public."Votes" AS v2 ON c._id = v2."documentId"
    WHERE
      c."parentCommentId" = $4
      AND v1."userId" = $1
      AND v1."extendedVoteType"->'reacts'->0->>'vote' = 'created'
      AND v1.cancelled IS NOT TRUE
      AND v1."isUnvote" IS NOT TRUE
      AND v2."userId" = $2
      AND v2."extendedVoteType"->'reacts'->0->>'vote' = 'created'
      AND v2.cancelled IS NOT TRUE
      AND v2."isUnvote" IS NOT TRUE
      AND v1."extendedVoteType"->'reacts'->0->>'react' != v2."extendedVoteType"->'reacts'->0->>'react'
    ORDER BY c."baseScore" DESC
    LIMIT $3
    `, [userId, targetUserId, limit, pollCommentId]);
  }

  async getPopularComments({
    minScore = 15,
    offset = 0,
    limit = 3,
    recencyFactor = 250000,
    recencyBias = 60 * 60 * 2,
  }: {
    offset?: number,
    limit?: number,
    minScore?: number,
    // The factor to divide age by for the recency bonus
    recencyFactor?: number,
    // The minimum age that a post will be considered as having, to avoid
    // over selecting brand new comments - defaults to 2 hours
    recencyBias?: number,
  }): Promise<DbComment[]> {
    const excludedTagId = forumSelect({
      EAForum: EA_FORUM_COMMUNITY_TOPIC_ID,
      default: null
    });

    const excludeTagId = !!excludedTagId;
    const excludedTagIdParam = excludeTagId ? { excludedTagId } : {};
    const excludedTagIdCondition = excludeTagId ? 'AND COALESCE((p."tagRelevance"->$(excludedTagId))::INTEGER, 0) < 1' : '';

    const lookbackPeriod = isAF ? '1 month' : '1 week';
    const afCommentsFilter = isAF ? 'AND "af" IS TRUE' : '';
    
    return this.any(`
      -- CommentsRepo.getPopularComments
      SELECT c.*
      FROM (
        SELECT DISTINCT ON ("postId") "_id"
        FROM "Comments"
        WHERE
          CURRENT_TIMESTAMP - "postedAt" < $(lookbackPeriod)::INTERVAL AND
          "shortform" IS NOT TRUE AND
          "baseScore" >= $(minScore) AND
          "retracted" IS NOT TRUE AND
          "deleted" IS NOT TRUE AND
          "deletedPublic" IS NOT TRUE AND
          "needsReview" IS NOT TRUE
          ${afCommentsFilter}
        ORDER BY "postId", "baseScore" DESC
      ) q
      JOIN "Comments" c ON c."_id" = q."_id"
      JOIN "Posts" p ON c."postId" = p."_id"
      WHERE
        p."hideFromPopularComments" IS NOT TRUE
        AND ${getViewablePostsSelector('p')}
        ${excludedTagIdCondition}
      ORDER BY c."baseScore" * EXP((EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - c."postedAt") + $(recencyBias)) / -$(recencyFactor)) DESC
      OFFSET $(offset)
      LIMIT $(limit)
    `, {
      minScore,
      offset,
      limit,
      recencyFactor,
      recencyBias,
      lookbackPeriod,
      ...excludedTagIdParam,
    });
  }

  private getSearchDocumentQuery(): string {
    return `
      -- CommentsRepo.getSearchDocumentQuery
      SELECT
        c."_id",
        c."_id" AS "objectID",
        c."userId",
        COALESCE(c."baseScore", 0) AS "baseScore",
        COALESCE(c."deleted", FALSE) AS "deleted",
        COALESCE(c."rejected", FALSE) AS "rejected",
        COALESCE(c."authorIsUnreviewed", FALSE) AS "authorIsUnreviewed",
        COALESCE(c."retracted", FALSE) AS "retracted",
        COALESCE(c."spam", FALSE) AS "spam",
        c."legacy",
        c."createdAt",
        c."postedAt",
        EXTRACT(EPOCH FROM c."postedAt") * 1000 AS "publicDateMs",
        COALESCE(c."af", FALSE) AS "af",
        CASE
          WHEN author."deleted" THEN NULL
          ELSE author."slug"
        END AS "authorSlug",
        CASE
          WHEN author."deleted" THEN NULL
          ELSE author."displayName"
        END AS "authorDisplayName",
        CASE
          WHEN author."deleted" THEN NULL
          ELSE author."username"
        END AS "authorUserName",
        c."postId",
        post."title" AS "postTitle",
        post."slug" AS "postSlug",
        COALESCE(post."isEvent", FALSE) AS "postIsEvent",
        post."groupId" AS "postGroupId",
        fm_post_tag_ids(post."_id") AS "tags",
        CASE WHEN c."tagId" IS NULL
          THEN fm_post_tag_ids(post."_id")
          ELSE ARRAY(SELECT c."tagId")
        END AS "tags",
        c."tagId",
        tag."name" AS "tagName",
        tag."slug" AS "tagSlug",
        c."tagCommentType",
        c."contents"->>'html' AS "body",
        NOW() AS "exportedAt"
      FROM "Comments" c
      LEFT JOIN "Users" author ON c."userId" = author."_id"
      LEFT JOIN "Posts" post on c."postId" = post."_id"
      LEFT JOIN "Tags" tag on c."tagId" = tag."_id"
    `;
  }

  getSearchDocumentById(id: string): Promise<SearchComment> {
    return this.getRawDb().one(`
      -- CommentsRepo.getSearchDocumentById
      ${this.getSearchDocumentQuery()}
      WHERE c."_id" = $1
    `, [id]);
  }

  getSearchDocuments(limit: number, offset: number): Promise<SearchComment[]> {
    return this.getRawDb().any(`
      -- CommentsRepo.getSearchDocuments
      ${this.getSearchDocumentQuery()}
      ORDER BY c."createdAt" DESC
      LIMIT $1
      OFFSET $2
    `, [limit, offset]);
  }

  async countSearchDocuments(): Promise<number> {
    const {count} = await this.getRawDb().one(`
      -- CommentsRepo.countSearchDocuents
      SELECT COUNT(*) FROM "Comments"
    `);
    return count;
  }

  async getCommentsPerDay({ postIds, startDate, endDate }: { postIds: string[]; startDate?: Date; endDate: Date; }): Promise<{ window_start_key: string; comment_count: string }[]> {
    return await this.getRawDb().any<{window_start_key: string, comment_count: string}>(`
      -- CommentsRepo.getCommentsPerDay
      SELECT
        -- Format as YYYY-MM-DD to make grouping easier
        to_char(c."postedAt", 'YYYY-MM-DD') AS window_start_key,
        COUNT(c."postedAt") AS comment_count
      FROM "Comments" c
      WHERE
        c."postId" IN ($1:csv)
        AND ($2 IS NULL OR c."postedAt" >= $2)
        AND c."postedAt" <= $3
        AND c."deleted" IS NOT TRUE
      GROUP BY
        window_start_key
      ORDER BY
        window_start_key;
    `, [postIds, startDate, endDate]);
  }

  async getUsersRecommendedCommentsOfTargetUser(userId: string, targetUserId: string, limit = 20): Promise<DbComment[]> {
    return this.any(`
      -- CommentsRepo.getUsersRecommendedCommentsOfTargetUser
      SELECT c.*
      FROM "ReadStatuses" AS rs
      INNER JOIN "Posts" AS p ON rs."postId" = p._id
      INNER JOIN "Comments" AS c ON c."postId" = p._id
      WHERE
          rs."userId" = $1
          AND c."userId" = $2
          AND rs."isRead" IS TRUE
          AND c."baseScore" > 7
      ORDER BY rs."lastUpdated" DESC
      LIMIT $3
    `, [userId, targetUserId, limit]);
  }

  async getCommentsWithElicitData(): Promise<DbComment[]> {
    return await this.any(`
      -- CommentsRepo.getCommentsWithElicitData
      SELECT *
      FROM "Comments"
      WHERE contents->>'html' LIKE '%elicit-binary-prediction%'
    `);
  }

  /**
   * Returns the number of comments that a user has authored in a given year, and their percentile among all users who
   * authored at least one comment in that year (for either regular comments or shortform). This is currently used for Wrapped.
   */
  async getAuthorshipStats({
    userId,
    year,
    shortform,
  }: {
    userId: string;
    year: number;
    shortform: boolean;
  }): Promise<{ totalCount: number; percentile: number }> {
    const startPostedAt = new Date(year, 0).toISOString();
    const endPostedAt = new Date(year + 1, 0).toISOString();
    const shortformCondition = shortform
      ? `"shortform" IS TRUE AND "topLevelCommentId" IS NULL`
      : `("shortform" IS FALSE OR "topLevelCommentId" IS NOT NULL)`;

    const result = await this.getRawDb().oneOrNone<{ total_count: string; percentile: number }>(
      `
      -- CommentsRepo.getAuthorshipStats
      WITH comment_counts AS (
        SELECT
          "userId",
          count(*) AS total_count
        FROM
          "Comments"
        WHERE
          "deleted" IS FALSE
          AND "postId" IS NOT NULL
          AND "needsReview" IS NOT TRUE
          AND "retracted" IS NOT TRUE
          AND "deletedPublic" IS NOT TRUE
          AND "moderatorHat" IS NOT TRUE
          AND ${shortformCondition}
          AND "postedAt" > $1
          AND "postedAt" < $2
        GROUP BY
          "userId"
      ), authorship_percentiles AS (
        SELECT
          "userId",
          slug,
          total_count,
          percent_rank() OVER (ORDER BY total_count ASC) percentile
        FROM
          comment_counts
          left join "Users" u on "userId" = u._id
        ORDER BY
          percentile DESC
      )
      SELECT
        total_count AS total_count,
        percentile
      FROM
        authorship_percentiles
      WHERE
        "userId" = $3;
    `,
      [startPostedAt, endPostedAt, userId]
    );

    return {
      totalCount: result?.total_count ? parseInt(result.total_count) : 0,
      percentile: result?.percentile ?? 0,
    };
  }
}

recordPerfMetrics(CommentsRepo);

export default CommentsRepo;

1	import Comments from "../../lib/collections/comments/collection";	1,917✔
2	import AbstractRepo from "./AbstractRepo";	20✔
3	import SelectQuery from "@/server/sql/SelectQuery";	20✔
4	import keyBy from 'lodash/keyBy';	20✔
5	import groupBy from 'lodash/groupBy';	20✔
6	import orderBy from 'lodash/orderBy';	20✔
7	import { filterWhereFieldsNotNull } from "../../lib/utils/typeGuardUtils";	20✔
8	import { EA_FORUM_COMMUNITY_TOPIC_ID } from "../../lib/collections/tags/collection";	20✔
9	import { recordPerfMetrics } from "./perfMetricWrapper";	20✔
10	import { forumSelect } from "../../lib/forumTypeUtils";	20✔
11	import { isAF } from "../../lib/instanceSettings";	20✔
12	import { getViewablePostsSelector } from "./helpers";	20✔
13
14	type ExtendedCommentWithReactions = DbComment & {
15	yourVote?: string,
16	theirVote?: string,
17	userVote?: string,
18	}
19
20	class CommentsRepo extends AbstractRepo<"Comments"> {
21	constructor() {
22	super(Comments);	1,857✔
23	}
24
25	async getPromotedCommentsOnPosts(postIds: string[]): Promise<(DbComment\|null)[]> {
26	const rawComments = await this.manyOrNone(`	×
27	-- CommentsRepo.getPromotedCommentsOnPosts
28	SELECT c.*
29	FROM "Comments" c
30	JOIN (
31	SELECT "postId", MAX("promotedAt") AS max_promotedAt
32	FROM "Comments"
33	WHERE "postId" IN ($1:csv)
34	AND "promotedAt" IS NOT NULL
35	GROUP BY "postId"
36	) sq
37	ON c."postId" = sq."postId" AND c."promotedAt" = sq.max_promotedAt;
38	`, [postIds]);
39
40	const comments = filterWhereFieldsNotNull(rawComments, "postId");	×
41	const commentsByPost = keyBy(comments, c=>c.postId);	×
42	return postIds.map(postId => commentsByPost[postId] ?? null);	×
43	}
44
45	async getRecentCommentsOnPosts(postIds: string[], limit: number, filter: MongoSelector<DbComment>): Promise<DbComment[][]> {
46	const selectQuery = new SelectQuery(this.getCollection().getTable(), filter)	×
47	const selectQueryAtoms = selectQuery.compileSelector(filter);	×
48	const {sql: filterWhereClause, args: filterArgs} = selectQuery.compileAtoms(selectQueryAtoms, 2);	×
49
50	const comments = await this.manyOrNone(`	×
51	-- CommentsRepo.getRecentCommentsOnPosts
52	WITH cte AS (
53	SELECT
54	comment_with_rownumber.*,
55	ROW_NUMBER() OVER (PARTITION BY comment_with_rownumber."postId" ORDER BY comment_with_rownumber."postedAt" DESC) as rn
56	FROM "Comments" comment_with_rownumber
57	WHERE comment_with_rownumber."postId" IN ($1:csv)
58	AND (
59	${filterWhereClause}
60	)
61	)
62	SELECT *
63	FROM cte
64	WHERE rn <= $2
65	`, [postIds, limit, ...filterArgs]);
66
67	const commentsByPost = groupBy(comments, c=>c.postId);	×
68	return postIds.map(postId =>	×
69	orderBy(	×
70	commentsByPost[postId] ?? [],	×
71	c => -c.postedAt.getTime()	×
72	)
73	);
74	}
75
76	async getCommentsWithReacts(limit: number): Promise<(DbComment\|null)[]> {
77	return await this.manyOrNone(`	×
78	-- CommentsRepo.getCommentsWithReacts
79	SELECT c.*
80	FROM "Comments" c
81	JOIN (
82	SELECT "documentId", MIN("votedAt") AS most_recent_react
83	FROM "Votes"
84	WHERE "collectionName" = 'Comments' AND "extendedVoteType"->'reacts' != '[]'::jsonb
85	GROUP BY "documentId"
86	ORDER BY most_recent_react DESC
87	LIMIT $1
88	) v
89	ON c._id = v."documentId"
90	ORDER BY v.most_recent_react DESC;
91	`, [limit]);
92	}
93
94	async getPopularPollComments (limit: number, pollCommentId: string): Promise<(ExtendedCommentWithReactions)[]> {
95	return await this.getRawDb().manyOrNone(`	×
96	-- CommentsRepo.getPopularPollComments
97	SELECT c.*
98	FROM public."Comments" AS c
99	WHERE c."parentCommentId" = $2
100	ORDER BY c."baseScore" DESC
101	LIMIT $1
102	`, [limit, pollCommentId]);
103	}
104
105	async getPopularPollCommentsWithUserVotes (userId: string, limit: number, pollCommentId: string): Promise<(ExtendedCommentWithReactions)[]> {
106	return await this.getRawDb().manyOrNone(`	×
107	-- CommentsRepo.getPopularPollCommentsWithUserVotes
108	SELECT c.*, v."extendedVoteType"->'reacts'->0->>'react' AS "yourVote"
109	FROM public."Comments" AS c
110	INNER JOIN public."Votes" AS v ON c._id = v."documentId"
111	WHERE
112	c."parentCommentId" = $3
113	AND v."userId" = $1
114	AND v."extendedVoteType"->'reacts'->0->>'vote' = 'created'
115	AND v.cancelled IS NOT TRUE
116	AND v."isUnvote" IS NOT TRUE
117	ORDER BY c."baseScore" DESC
118	LIMIT $2
119	`, [userId, limit, pollCommentId]);
120	}
121
122	async getPopularPollCommentsWithTwoUserVotes (userId: string, targetUserId: string, limit: number, pollCommentId: string): Promise<(ExtendedCommentWithReactions)[]> {
123	return await this.getRawDb().manyOrNone(`	×
124	-- CommentsRepo.getPopularPollCommentsWithTwoUserVotes
125	SELECT c.*,
126	v1."extendedVoteType"->'reacts'->0->>'react' AS "yourVote",
127	v2."extendedVoteType"->'reacts'->0->>'react' AS "theirVote"
128	FROM public."Comments" AS c
129	LEFT JOIN public."Votes" AS v1 ON c._id = v1."documentId"
130	LEFT JOIN public."Votes" AS v2 ON c._id = v2."documentId"
131	WHERE
132	c."parentCommentId" = $4
133	AND v1."userId" = $1
134	AND v1."extendedVoteType"->'reacts'->0->>'vote' = 'created'
135	AND v1.cancelled IS NOT TRUE
136	AND v1."isUnvote" IS NOT TRUE
137	AND v2."userId" = $2
138	AND v2."extendedVoteType"->'reacts'->0->>'vote' = 'created'
139	AND v2.cancelled IS NOT TRUE
140	AND v2."isUnvote" IS NOT TRUE
141	AND v1."extendedVoteType"->'reacts'->0->>'react' != v2."extendedVoteType"->'reacts'->0->>'react'
142	ORDER BY c."baseScore" DESC
143	LIMIT $3
144	`, [userId, targetUserId, limit, pollCommentId]);
145	}
146
147	async getPopularComments({
148	minScore = 15,	×
149	offset = 0,	×
150	limit = 3,	×
151	recencyFactor = 250000,	×
152	recencyBias = 60 * 60 * 2,	×
153	}: {
154	offset?: number,
155	limit?: number,
156	minScore?: number,
157	// The factor to divide age by for the recency bonus
158	recencyFactor?: number,
159	// The minimum age that a post will be considered as having, to avoid
160	// over selecting brand new comments - defaults to 2 hours
161	recencyBias?: number,
162	}): Promise<DbComment[]> {
163	const excludedTagId = forumSelect({	×
164	EAForum: EA_FORUM_COMMUNITY_TOPIC_ID,
165	default: null
166	});
167
168	const excludeTagId = !!excludedTagId;	×
169	const excludedTagIdParam = excludeTagId ? { excludedTagId } : {};	×
170	const excludedTagIdCondition = excludeTagId ? 'AND COALESCE((p."tagRelevance"->$(excludedTagId))::INTEGER, 0) < 1' : '';	×
171
172	const lookbackPeriod = isAF ? '1 month' : '1 week';	×
173	const afCommentsFilter = isAF ? 'AND "af" IS TRUE' : '';	×
174
175	return this.any(`	×
176	-- CommentsRepo.getPopularComments
177	SELECT c.*
178	FROM (
179	SELECT DISTINCT ON ("postId") "_id"
180	FROM "Comments"
181	WHERE
182	CURRENT_TIMESTAMP - "postedAt" < $(lookbackPeriod)::INTERVAL AND
183	"shortform" IS NOT TRUE AND
184	"baseScore" >= $(minScore) AND
185	"retracted" IS NOT TRUE AND
186	"deleted" IS NOT TRUE AND
187	"deletedPublic" IS NOT TRUE AND
188	"needsReview" IS NOT TRUE
189	${afCommentsFilter}
190	ORDER BY "postId", "baseScore" DESC
191	) q
192	JOIN "Comments" c ON c."_id" = q."_id"
193	JOIN "Posts" p ON c."postId" = p."_id"
194	WHERE
195	p."hideFromPopularComments" IS NOT TRUE
196	AND ${getViewablePostsSelector('p')}
197	${excludedTagIdCondition}
198	ORDER BY c."baseScore" * EXP((EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - c."postedAt") + $(recencyBias)) / -$(recencyFactor)) DESC
199	OFFSET $(offset)
200	LIMIT $(limit)
201	`, {
202	minScore,
203	offset,
204	limit,
205	recencyFactor,
206	recencyBias,
207	lookbackPeriod,
208	...excludedTagIdParam,
209	});
210	}
211
212	private getSearchDocumentQuery(): string {
UNCOV 213	return `	×
214	-- CommentsRepo.getSearchDocumentQuery
215	SELECT
216	c."_id",
217	c."_id" AS "objectID",
218	c."userId",
219	COALESCE(c."baseScore", 0) AS "baseScore",
220	COALESCE(c."deleted", FALSE) AS "deleted",
221	COALESCE(c."rejected", FALSE) AS "rejected",
222	COALESCE(c."authorIsUnreviewed", FALSE) AS "authorIsUnreviewed",
223	COALESCE(c."retracted", FALSE) AS "retracted",
224	COALESCE(c."spam", FALSE) AS "spam",
225	c."legacy",
226	c."createdAt",
227	c."postedAt",
228	EXTRACT(EPOCH FROM c."postedAt") * 1000 AS "publicDateMs",
229	COALESCE(c."af", FALSE) AS "af",
230	CASE
231	WHEN author."deleted" THEN NULL
232	ELSE author."slug"
233	END AS "authorSlug",
234	CASE
235	WHEN author."deleted" THEN NULL
236	ELSE author."displayName"
237	END AS "authorDisplayName",
238	CASE
239	WHEN author."deleted" THEN NULL
240	ELSE author."username"
241	END AS "authorUserName",
242	c."postId",
243	post."title" AS "postTitle",
244	post."slug" AS "postSlug",
245	COALESCE(post."isEvent", FALSE) AS "postIsEvent",
246	post."groupId" AS "postGroupId",
247	fm_post_tag_ids(post."_id") AS "tags",
248	CASE WHEN c."tagId" IS NULL
249	THEN fm_post_tag_ids(post."_id")
250	ELSE ARRAY(SELECT c."tagId")
251	END AS "tags",
252	c."tagId",
253	tag."name" AS "tagName",
254	tag."slug" AS "tagSlug",
255	c."tagCommentType",
256	c."contents"->>'html' AS "body",
257	NOW() AS "exportedAt"
258	FROM "Comments" c
259	LEFT JOIN "Users" author ON c."userId" = author."_id"
260	LEFT JOIN "Posts" post on c."postId" = post."_id"
261	LEFT JOIN "Tags" tag on c."tagId" = tag."_id"
262	`;
263	}
264
265	getSearchDocumentById(id: string): Promise<SearchComment> {
UNCOV 266	return this.getRawDb().one(`	×
267	-- CommentsRepo.getSearchDocumentById
268	${this.getSearchDocumentQuery()}
269	WHERE c."_id" = $1
270	`, [id]);
271	}
272
273	getSearchDocuments(limit: number, offset: number): Promise<SearchComment[]> {
UNCOV 274	return this.getRawDb().any(`	×
275	-- CommentsRepo.getSearchDocuments
276	${this.getSearchDocumentQuery()}
277	ORDER BY c."createdAt" DESC
278	LIMIT $1
279	OFFSET $2
280	`, [limit, offset]);
281	}
282
283	async countSearchDocuments(): Promise<number> {
UNCOV 284	const {count} = await this.getRawDb().one(`	×
285	-- CommentsRepo.countSearchDocuents
286	SELECT COUNT(*) FROM "Comments"
287	`);
UNCOV 288	return count;	×
289	}
290
291	async getCommentsPerDay({ postIds, startDate, endDate }: { postIds: string[]; startDate?: Date; endDate: Date; }): Promise<{ window_start_key: string; comment_count: string }[]> {
UNCOV 292	return await this.getRawDb().any<{window_start_key: string, comment_count: string}>(`	×
293	-- CommentsRepo.getCommentsPerDay
294	SELECT
295	-- Format as YYYY-MM-DD to make grouping easier
296	to_char(c."postedAt", 'YYYY-MM-DD') AS window_start_key,
297	COUNT(c."postedAt") AS comment_count
298	FROM "Comments" c
299	WHERE
300	c."postId" IN ($1:csv)
301	AND ($2 IS NULL OR c."postedAt" >= $2)
302	AND c."postedAt" <= $3
303	AND c."deleted" IS NOT TRUE
304	GROUP BY
305	window_start_key
306	ORDER BY
307	window_start_key;
308	`, [postIds, startDate, endDate]);
309	}
310
311	async getUsersRecommendedCommentsOfTargetUser(userId: string, targetUserId: string, limit = 20): Promise<DbComment[]> {	×
UNCOV 312	return this.any(`	×
313	-- CommentsRepo.getUsersRecommendedCommentsOfTargetUser
314	SELECT c.*
315	FROM "ReadStatuses" AS rs
316	INNER JOIN "Posts" AS p ON rs."postId" = p._id
317	INNER JOIN "Comments" AS c ON c."postId" = p._id
318	WHERE
319	rs."userId" = $1
320	AND c."userId" = $2
321	AND rs."isRead" IS TRUE
322	AND c."baseScore" > 7
323	ORDER BY rs."lastUpdated" DESC
324	LIMIT $3
325	`, [userId, targetUserId, limit]);
326	}
327
328	async getCommentsWithElicitData(): Promise<DbComment[]> {
UNCOV 329	return await this.any(`	×
330	-- CommentsRepo.getCommentsWithElicitData
331	SELECT *
332	FROM "Comments"
333	WHERE contents->>'html' LIKE '%elicit-binary-prediction%'
334	`);
335	}
336
337	/**
338	* Returns the number of comments that a user has authored in a given year, and their percentile among all users who
339	* authored at least one comment in that year (for either regular comments or shortform). This is currently used for Wrapped.
340	*/
341	async getAuthorshipStats({
342	userId,
343	year,
344	shortform,
345	}: {
346	userId: string;
347	year: number;
348	shortform: boolean;
349	}): Promise<{ totalCount: number; percentile: number }> {
UNCOV 350	const startPostedAt = new Date(year, 0).toISOString();	×
351	const endPostedAt = new Date(year + 1, 0).toISOString();	×
352	const shortformCondition = shortform	×
353	? `"shortform" IS TRUE AND "topLevelCommentId" IS NULL`	×
354	: `("shortform" IS FALSE OR "topLevelCommentId" IS NOT NULL)`;
355
UNCOV 356	const result = await this.getRawDb().oneOrNone<{ total_count: string; percentile: number }>(	×
357	`
358	-- CommentsRepo.getAuthorshipStats
359	WITH comment_counts AS (
360	SELECT
361	"userId",
362	count(*) AS total_count
363	FROM
364	"Comments"
365	WHERE
366	"deleted" IS FALSE
367	AND "postId" IS NOT NULL
368	AND "needsReview" IS NOT TRUE
369	AND "retracted" IS NOT TRUE
370	AND "deletedPublic" IS NOT TRUE
371	AND "moderatorHat" IS NOT TRUE
372	AND ${shortformCondition}
373	AND "postedAt" > $1
374	AND "postedAt" < $2
375	GROUP BY
376	"userId"
377	), authorship_percentiles AS (
378	SELECT
379	"userId",
380	slug,
381	total_count,
382	percent_rank() OVER (ORDER BY total_count ASC) percentile
383	FROM
384	comment_counts
385	left join "Users" u on "userId" = u._id
386	ORDER BY
387	percentile DESC
388	)
389	SELECT
390	total_count AS total_count,
391	percentile
392	FROM
393	authorship_percentiles
394	WHERE
395	"userId" = $3;
396	`,
397	[startPostedAt, endPostedAt, userId]
398	);
399
UNCOV 400	return {	×
401	totalCount: result?.total_count ? parseInt(result.total_count) : 0,	×
402	percentile: result?.percentile ?? 0,	×
403	};
404	}
405	}	20✔
406
407	recordPerfMetrics(CommentsRepo);
408
409	export default CommentsRepo;	20✔

ForumMagnum / ForumMagnum / 10603810623

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous