8883143192

Committed 29 Apr 2024 05:54PM UTC coverage: 31.945% (-0.007%) from 31.952%

Build # 8883143192

Build Type

Pull #9198

github

Committed by

oetherington

Commit Message

Reindex posts, comments and sequences for deleted users

Pull Request Pull Request #9198: Reindex context in elastic for deleted users

Run Details

4714 of 38552 branches covered (12.23%)

Branch coverage included in aggregate %.

1 of 19 new or added lines in 2 files covered. (5.26%)

30 existing lines in 3 files now uncovered.

27503 of 62298 relevant lines covered (44.15%)

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

ForumMagnum / ForumMagnum / 8883143192

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