10710605934

Committed 04 Sep 2024 10:40PM UTC coverage: 31.867% (+0.008%) from 31.859%

Build # 10710605934

Build Type

Pull #9713

github

Committed by

web-flow

Commit Message

Merge 4c2a9410f into 7d567d8ee

Pull Request Pull Request #9713: Merge master into LW Deploy

Run Details

5074 of 41300 branches covered (12.29%)

Branch coverage included in aggregate %.

49 of 88 new or added lines in 14 files covered. (55.68%)

180 existing lines in 10 files now uncovered.

29409 of 66909 relevant lines covered (43.95%)

185.02 hits per line

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

17.72

/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 p."frontpageDate" IS NOT NULL
        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";	84✔
2	import AbstractRepo from "./AbstractRepo";	21✔
3	import SelectQuery from "@/server/sql/SelectQuery";	21✔
4	import keyBy from 'lodash/keyBy';	21✔
5	import groupBy from 'lodash/groupBy';	21✔
6	import orderBy from 'lodash/orderBy';	21✔
7	import { filterWhereFieldsNotNull } from "../../lib/utils/typeGuardUtils";	21✔
8	import { EA_FORUM_COMMUNITY_TOPIC_ID } from "../../lib/collections/tags/collection";	21✔
9	import { recordPerfMetrics } from "./perfMetricWrapper";	21✔
10	import { forumSelect } from "../../lib/forumTypeUtils";	21✔
11	import { isAF } from "../../lib/instanceSettings";	21✔
12	import { getViewablePostsSelector } from "./helpers";	21✔
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);	×
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)[]> {
UNCOV 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[]> {
UNCOV 163	const excludedTagId = forumSelect({	×
164	EAForum: EA_FORUM_COMMUNITY_TOPIC_ID,
165	default: null
166	});
167
UNCOV 168	const excludeTagId = !!excludedTagId;	×
UNCOV 169	const excludedTagIdParam = excludeTagId ? { excludedTagId } : {};	×
UNCOV 170	const excludedTagIdCondition = excludeTagId ? 'AND COALESCE((p."tagRelevance"->$(excludedTagId))::INTEGER, 0) < 1' : '';	×
171
172	const lookbackPeriod = isAF ? '1 month' : '1 week';	×
UNCOV 173	const afCommentsFilter = isAF ? 'AND "af" IS TRUE' : '';	×
174
UNCOV 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 p."frontpageDate" IS NOT NULL
197	AND ${getViewablePostsSelector('p')}
198	${excludedTagIdCondition}
199	ORDER BY c."baseScore" * EXP((EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - c."postedAt") + $(recencyBias)) / -$(recencyFactor)) DESC
200	OFFSET $(offset)
201	LIMIT $(limit)
202	`, {
203	minScore,
204	offset,
205	limit,
206	recencyFactor,
207	recencyBias,
208	lookbackPeriod,
209	...excludedTagIdParam,
210	});
211	}
212
213	private getSearchDocumentQuery(): string {
UNCOV 214	return `	×
215	-- CommentsRepo.getSearchDocumentQuery
216	SELECT
217	c."_id",
218	c."_id" AS "objectID",
219	c."userId",
220	COALESCE(c."baseScore", 0) AS "baseScore",
221	COALESCE(c."deleted", FALSE) AS "deleted",
222	COALESCE(c."rejected", FALSE) AS "rejected",
223	COALESCE(c."authorIsUnreviewed", FALSE) AS "authorIsUnreviewed",
224	COALESCE(c."retracted", FALSE) AS "retracted",
225	COALESCE(c."spam", FALSE) AS "spam",
226	c."legacy",
227	c."createdAt",
228	c."postedAt",
229	EXTRACT(EPOCH FROM c."postedAt") * 1000 AS "publicDateMs",
230	COALESCE(c."af", FALSE) AS "af",
231	CASE
232	WHEN author."deleted" THEN NULL
233	ELSE author."slug"
234	END AS "authorSlug",
235	CASE
236	WHEN author."deleted" THEN NULL
237	ELSE author."displayName"
238	END AS "authorDisplayName",
239	CASE
240	WHEN author."deleted" THEN NULL
241	ELSE author."username"
242	END AS "authorUserName",
243	c."postId",
244	post."title" AS "postTitle",
245	post."slug" AS "postSlug",
246	COALESCE(post."isEvent", FALSE) AS "postIsEvent",
247	post."groupId" AS "postGroupId",
248	fm_post_tag_ids(post."_id") AS "tags",
249	CASE WHEN c."tagId" IS NULL
250	THEN fm_post_tag_ids(post."_id")
251	ELSE ARRAY(SELECT c."tagId")
252	END AS "tags",
253	c."tagId",
254	tag."name" AS "tagName",
255	tag."slug" AS "tagSlug",
256	c."tagCommentType",
257	c."contents"->>'html' AS "body",
258	NOW() AS "exportedAt"
259	FROM "Comments" c
260	LEFT JOIN "Users" author ON c."userId" = author."_id"
261	LEFT JOIN "Posts" post on c."postId" = post."_id"
262	LEFT JOIN "Tags" tag on c."tagId" = tag."_id"
263	`;
264	}
265
266	getSearchDocumentById(id: string): Promise<SearchComment> {
UNCOV 267	return this.getRawDb().one(`	×
268	-- CommentsRepo.getSearchDocumentById
269	${this.getSearchDocumentQuery()}
270	WHERE c."_id" = $1
271	`, [id]);
272	}
273
274	getSearchDocuments(limit: number, offset: number): Promise<SearchComment[]> {
UNCOV 275	return this.getRawDb().any(`	×
276	-- CommentsRepo.getSearchDocuments
277	${this.getSearchDocumentQuery()}
278	ORDER BY c."createdAt" DESC
279	LIMIT $1
280	OFFSET $2
281	`, [limit, offset]);
282	}
283
284	async countSearchDocuments(): Promise<number> {
UNCOV 285	const {count} = await this.getRawDb().one(`	×
286	-- CommentsRepo.countSearchDocuents
287	SELECT COUNT(*) FROM "Comments"
288	`);
UNCOV 289	return count;	×
290	}
291
292	async getCommentsPerDay({ postIds, startDate, endDate }: { postIds: string[]; startDate?: Date; endDate: Date; }): Promise<{ window_start_key: string; comment_count: string }[]> {
293	return await this.getRawDb().any<{window_start_key: string, comment_count: string}>(`	×
294	-- CommentsRepo.getCommentsPerDay
295	SELECT
296	-- Format as YYYY-MM-DD to make grouping easier
297	to_char(c."postedAt", 'YYYY-MM-DD') AS window_start_key,
298	COUNT(c."postedAt") AS comment_count
299	FROM "Comments" c
300	WHERE
301	c."postId" IN ($1:csv)
302	AND ($2 IS NULL OR c."postedAt" >= $2)
303	AND c."postedAt" <= $3
304	AND c."deleted" IS NOT TRUE
305	GROUP BY
306	window_start_key
307	ORDER BY
308	window_start_key;
309	`, [postIds, startDate, endDate]);
310	}
311
312	async getUsersRecommendedCommentsOfTargetUser(userId: string, targetUserId: string, limit = 20): Promise<DbComment[]> {	×
UNCOV 313	return this.any(`	×
314	-- CommentsRepo.getUsersRecommendedCommentsOfTargetUser
315	SELECT c.*
316	FROM "ReadStatuses" AS rs
317	INNER JOIN "Posts" AS p ON rs."postId" = p._id
318	INNER JOIN "Comments" AS c ON c."postId" = p._id
319	WHERE
320	rs."userId" = $1
321	AND c."userId" = $2
322	AND rs."isRead" IS TRUE
323	AND c."baseScore" > 7
324	ORDER BY rs."lastUpdated" DESC
325	LIMIT $3
326	`, [userId, targetUserId, limit]);
327	}
328
329	async getCommentsWithElicitData(): Promise<DbComment[]> {
UNCOV 330	return await this.any(`	×
331	-- CommentsRepo.getCommentsWithElicitData
332	SELECT *
333	FROM "Comments"
334	WHERE contents->>'html' LIKE '%elicit-binary-prediction%'
335	`);
336	}
337
338	/**
339	* Returns the number of comments that a user has authored in a given year, and their percentile among all users who
340	* authored at least one comment in that year (for either regular comments or shortform). This is currently used for Wrapped.
341	*/
342	async getAuthorshipStats({
343	userId,
344	year,
345	shortform,
346	}: {
347	userId: string;
348	year: number;
349	shortform: boolean;
350	}): Promise<{ totalCount: number; percentile: number }> {
UNCOV 351	const startPostedAt = new Date(year, 0).toISOString();	×
UNCOV 352	const endPostedAt = new Date(year + 1, 0).toISOString();	×
UNCOV 353	const shortformCondition = shortform	×
354	? `"shortform" IS TRUE AND "topLevelCommentId" IS NULL`	×
355	: `("shortform" IS FALSE OR "topLevelCommentId" IS NOT NULL)`;
356
UNCOV 357	const result = await this.getRawDb().oneOrNone<{ total_count: string; percentile: number }>(	×
358	`
359	-- CommentsRepo.getAuthorshipStats
360	WITH comment_counts AS (
361	SELECT
362	"userId",
363	count(*) AS total_count
364	FROM
365	"Comments"
366	WHERE
367	"deleted" IS FALSE
368	AND "postId" IS NOT NULL
369	AND "needsReview" IS NOT TRUE
370	AND "retracted" IS NOT TRUE
371	AND "deletedPublic" IS NOT TRUE
372	AND "moderatorHat" IS NOT TRUE
373	AND ${shortformCondition}
374	AND "postedAt" > $1
375	AND "postedAt" < $2
376	GROUP BY
377	"userId"
378	), authorship_percentiles AS (
379	SELECT
380	"userId",
381	slug,
382	total_count,
383	percent_rank() OVER (ORDER BY total_count ASC) percentile
384	FROM
385	comment_counts
386	left join "Users" u on "userId" = u._id
387	ORDER BY
388	percentile DESC
389	)
390	SELECT
391	total_count AS total_count,
392	percentile
393	FROM
394	authorship_percentiles
395	WHERE
396	"userId" = $3;
397	`,
398	[startPostedAt, endPostedAt, userId]
399	);
400
UNCOV 401	return {	×
402	totalCount: result?.total_count ? parseInt(result.total_count) : 0,	×
403	percentile: result?.percentile ?? 0,	×
404	};
405	}
406	}	21✔
407
408	recordPerfMetrics(CommentsRepo);
409
410	export default CommentsRepo;	21✔

ForumMagnum / ForumMagnum / 10710605934

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