From abac2f785a2e820f32914cbfa0fb00c4973fd703 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Sat, 24 May 2025 22:01:42 -0400 Subject: fix QueryService.generateMutedUserRenotesQueryForNotes to properly exclude quotes --- packages/backend/src/core/QueryService.ts | 11 +++++------ 1 file changed, 5 insertions(+), 6 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index 50a72e8aa6..e87360b00d 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -243,13 +243,12 @@ export class QueryService { q.andWhere(new Brackets(qb => { qb - .where(new Brackets(qb => { - qb.where('note.renoteId IS NOT NULL'); - qb.andWhere('note.text IS NULL'); - qb.andWhere(`note.userId NOT IN (${ mutingQuery.getQuery() })`); - })) .orWhere('note.renoteId IS NULL') - .orWhere('note.text IS NOT NULL'); + .orWhere('note.text IS NOT NULL') + .orWhere('note.cw IS NOT NULL') + .orWhere('note.replyId IS NOT NULL') + .orWhere('note.fileIds != \'{}\'') + .orWhere(`note.userId NOT IN (${ mutingQuery.getQuery() })`); })); q.setParameters(mutingQuery.getParameters()); -- cgit v1.2.3-freya From 70641501444820b2e30c01ae152bf396e8cabf7e Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Sat, 24 May 2025 23:14:57 -0400 Subject: use instance block columns instead of checking meta columns --- .../src/core/FanoutTimelineEndpointService.ts | 11 ++++-- packages/backend/src/core/QueryService.ts | 46 ++++++++++------------ .../backend/src/core/chart/charts/federation.ts | 23 ++++++----- .../src/core/entities/InstanceEntityService.ts | 6 +-- .../src/server/api/endpoints/antennas/notes.ts | 1 + .../src/server/api/endpoints/channels/timeline.ts | 2 + .../src/server/api/endpoints/clips/notes.ts | 3 +- .../server/api/endpoints/notes/bubble-timeline.ts | 2 +- .../server/api/endpoints/notes/search-by-tag.ts | 5 +-- .../src/server/api/endpoints/roles/notes.ts | 2 +- .../src/server/api/endpoints/users/reactions.ts | 7 +++- 11 files changed, 56 insertions(+), 52 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/FanoutTimelineEndpointService.ts b/packages/backend/src/core/FanoutTimelineEndpointService.ts index af2723e99d..f9cf41e854 100644 --- a/packages/backend/src/core/FanoutTimelineEndpointService.ts +++ b/packages/backend/src/core/FanoutTimelineEndpointService.ts @@ -136,10 +136,10 @@ export class FanoutTimelineEndpointService { const parentFilter = filter; filter = (note) => { if (!ps.ignoreAuthorFromInstanceBlock) { - if (this.utilityService.isBlockedHost(this.meta.blockedHosts, note.userHost)) return false; + if (note.userInstance?.isBlocked) return false; } - if (note.userId !== note.renoteUserId && this.utilityService.isBlockedHost(this.meta.blockedHosts, note.renoteUserHost)) return false; - if (note.userId !== note.replyUserId && this.utilityService.isBlockedHost(this.meta.blockedHosts, note.replyUserHost)) return false; + if (note.userId !== note.renoteUserId && note.renoteUserInstance?.isBlocked) return false; + if (note.userId !== note.replyUserId && note.replyUserInstance?.isBlocked) return false; return parentFilter(note); }; @@ -194,7 +194,10 @@ export class FanoutTimelineEndpointService { .leftJoinAndSelect('note.renote', 'renote') .leftJoinAndSelect('reply.user', 'replyUser') .leftJoinAndSelect('renote.user', 'renoteUser') - .leftJoinAndSelect('note.channel', 'channel'); + .leftJoinAndSelect('note.channel', 'channel') + .leftJoinAndSelect('note.userInstance', 'userInstance') + .leftJoinAndSelect('note.replyUserInstance', 'replyUserInstance') + .leftJoinAndSelect('note.renoteUserInstance', 'renoteUserInstance'); const notes = (await query.getMany()).filter(noteFilter); diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index e87360b00d..1b00f41d20 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -255,34 +255,28 @@ export class QueryService { } @bindThis - public generateBlockedHostQueryForNote(q: SelectQueryBuilder, excludeAuthor?: boolean): void { - let nonBlockedHostQuery: (part: string) => string; - if (this.meta.blockedHosts.length === 0) { - nonBlockedHostQuery = () => '1=1'; - } else { - nonBlockedHostQuery = (match: string) => `('.' || ${match}) NOT ILIKE ALL(select '%.' || x from (select unnest("blockedHosts") as x from "meta") t)`; - } + public generateBlockedHostQueryForNote(q: SelectQueryBuilder, excludeAuthor?: boolean, allowSilenced = true): void { + function checkFor(key: 'user' | 'replyUser' | 'renoteUser') { + q.leftJoin(`note.${key}Instance`, `${key}Instance`); + q.andWhere(new Brackets(qb => { + qb.orWhere(`note.${key}Id IS NULL`) // no corresponding user + .orWhere(`note.${key}Host IS NULL`) // local + .orWhere(`${key}Instance.isBlocked = false`); // not blocked - if (excludeAuthor) { - const instanceSuspension = (user: string) => new Brackets(qb => qb - .where(`note.${user}Id IS NULL`) // no corresponding user - .orWhere(`note.userId = note.${user}Id`) - .orWhere(`note.${user}Host IS NULL`) // local - .orWhere(nonBlockedHostQuery(`note.${user}Host`))); + if (!allowSilenced) { + qb.orWhere(`${key}Instance.isSilenced = false`); // not silenced + } - q - .andWhere(instanceSuspension('replyUser')) - .andWhere(instanceSuspension('renoteUser')); - } else { - const instanceSuspension = (user: string) => new Brackets(qb => qb - .where(`note.${user}Id IS NULL`) // no corresponding user - .orWhere(`note.${user}Host IS NULL`) // local - .orWhere(nonBlockedHostQuery(`note.${user}Host`))); - - q - .andWhere(instanceSuspension('user')) - .andWhere(instanceSuspension('replyUser')) - .andWhere(instanceSuspension('renoteUser')); + if (excludeAuthor) { + qb.orWhere(`note.userId = note.${key}Id`); // author + } + })); + } + + if (!excludeAuthor) { + checkFor('user'); } + checkFor('replyUser'); + checkFor('renoteUser'); } } diff --git a/packages/backend/src/core/chart/charts/federation.ts b/packages/backend/src/core/chart/charts/federation.ts index bf702884ca..b6db6f5454 100644 --- a/packages/backend/src/core/chart/charts/federation.ts +++ b/packages/backend/src/core/chart/charts/federation.ts @@ -44,10 +44,6 @@ export default class FederationChart extends Chart { // eslint-di } protected async tickMinor(): Promise>> { - const suspendedInstancesQuery = this.instancesRepository.createQueryBuilder('instance') - .select('instance.host') - .where('instance.suspensionState != \'none\''); - const pubsubSubQuery = this.followingsRepository.createQueryBuilder('f') .select('f.followerHost') .where('f.followerHost IS NOT NULL'); @@ -64,22 +60,25 @@ export default class FederationChart extends Chart { // eslint-di this.followingsRepository.createQueryBuilder('following') .select('COUNT(DISTINCT following.followeeHost)') .where('following.followeeHost IS NOT NULL') - .andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || following.followeeHost) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)') - .andWhere(`following.followeeHost NOT IN (${ suspendedInstancesQuery.getQuery() })`) + .innerJoin('following.followeeInstance', 'followeeInstance') + .andWhere('followeeInstance.suspensionState = \'none\'') + .andWhere('followeeInstance.isBlocked = false') .getRawOne() .then(x => parseInt(x.count, 10)), this.followingsRepository.createQueryBuilder('following') .select('COUNT(DISTINCT following.followerHost)') .where('following.followerHost IS NOT NULL') - .andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || following.followerHost) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)') - .andWhere(`following.followerHost NOT IN (${ suspendedInstancesQuery.getQuery() })`) + .innerJoin('following.followerInstance', 'followerInstance') + .andWhere('followerInstance.isBlocked = false') + .andWhere('followerInstance.suspensionState = \'none\'') .getRawOne() .then(x => parseInt(x.count, 10)), this.followingsRepository.createQueryBuilder('following') .select('COUNT(DISTINCT following.followeeHost)') .where('following.followeeHost IS NOT NULL') - .andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || following.followeeHost) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)') - .andWhere(`following.followeeHost NOT IN (${ suspendedInstancesQuery.getQuery() })`) + .innerJoin('following.followeeInstance', 'followeeInstance') + .andWhere('followeeInstance.isBlocked = false') + .andWhere('followeeInstance.suspensionState = \'none\'') .andWhere(`following.followeeHost IN (${ pubsubSubQuery.getQuery() })`) .setParameters(pubsubSubQuery.getParameters()) .getRawOne() @@ -87,7 +86,7 @@ export default class FederationChart extends Chart { // eslint-di this.instancesRepository.createQueryBuilder('instance') .select('COUNT(instance.id)') .where(`instance.host IN (${ subInstancesQuery.getQuery() })`) - .andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || instance.host) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)') + .andWhere('instance.isBlocked = false') .andWhere('instance.suspensionState = \'none\'') .andWhere('instance.isNotResponding = false') .getRawOne() @@ -95,7 +94,7 @@ export default class FederationChart extends Chart { // eslint-di this.instancesRepository.createQueryBuilder('instance') .select('COUNT(instance.id)') .where(`instance.host IN (${ pubInstancesQuery.getQuery() })`) - .andWhere(this.meta.blockedHosts.length === 0 ? '1=1' : '(\'.\' || instance.host) NOT ILIKE ALL(select \'%.\' || x from (select unnest("blockedHosts") as x from "meta") t)') + .andWhere('instance.isBlocked = false') .andWhere('instance.suspensionState = \'none\'') .andWhere('instance.isNotResponding = false') .getRawOne() diff --git a/packages/backend/src/core/entities/InstanceEntityService.ts b/packages/backend/src/core/entities/InstanceEntityService.ts index fcc9bed3bd..332d2943a4 100644 --- a/packages/backend/src/core/entities/InstanceEntityService.ts +++ b/packages/backend/src/core/entities/InstanceEntityService.ts @@ -43,7 +43,7 @@ export class InstanceEntityService { isNotResponding: instance.isNotResponding, isSuspended: instance.suspensionState !== 'none', suspensionState: instance.suspensionState, - isBlocked: this.utilityService.isBlockedHost(this.meta.blockedHosts, instance.host), + isBlocked: instance.isBlocked, softwareName: instance.softwareName, softwareVersion: instance.softwareVersion, openRegistrations: instance.openRegistrations, @@ -51,8 +51,8 @@ export class InstanceEntityService { description: instance.description, maintainerName: instance.maintainerName, maintainerEmail: instance.maintainerEmail, - isSilenced: this.utilityService.isSilencedHost(this.meta.silencedHosts, instance.host), - isMediaSilenced: this.utilityService.isMediaSilencedHost(this.meta.mediaSilencedHosts, instance.host), + isSilenced: instance.isSilenced, + isMediaSilenced: instance.isMediaSilenced, iconUrl: instance.iconUrl, faviconUrl: instance.faviconUrl, themeColor: instance.themeColor, diff --git a/packages/backend/src/server/api/endpoints/antennas/notes.ts b/packages/backend/src/server/api/endpoints/antennas/notes.ts index b90ba6aa0d..7e79f0dccc 100644 --- a/packages/backend/src/server/api/endpoints/antennas/notes.ts +++ b/packages/backend/src/server/api/endpoints/antennas/notes.ts @@ -121,6 +121,7 @@ export default class extends Endpoint { // eslint- this.queryService.generateVisibilityQuery(query, me); this.queryService.generateMutedUserQueryForNotes(query, me); this.queryService.generateBlockedUserQueryForNotes(query, me); + this.queryService.generateMutedUserRenotesQueryForNotes(query, me); const notes = await query.getMany(); if (sinceId != null && untilId == null) { diff --git a/packages/backend/src/server/api/endpoints/channels/timeline.ts b/packages/backend/src/server/api/endpoints/channels/timeline.ts index 6336f43e9f..99ae1c2211 100644 --- a/packages/backend/src/server/api/endpoints/channels/timeline.ts +++ b/packages/backend/src/server/api/endpoints/channels/timeline.ts @@ -138,9 +138,11 @@ export default class extends Endpoint { // eslint- .leftJoinAndSelect('note.channel', 'channel'); this.queryService.generateBlockedHostQueryForNote(query); + this.queryService.generateVisibilityQuery(query, me); if (me) { this.queryService.generateMutedUserQueryForNotes(query, me); this.queryService.generateBlockedUserQueryForNotes(query, me); + this.queryService.generateMutedUserRenotesQueryForNotes(query, me); } if (ps.withRenotes === false) { diff --git a/packages/backend/src/server/api/endpoints/clips/notes.ts b/packages/backend/src/server/api/endpoints/clips/notes.ts index 59513e530d..4758dbad00 100644 --- a/packages/backend/src/server/api/endpoints/clips/notes.ts +++ b/packages/backend/src/server/api/endpoints/clips/notes.ts @@ -92,10 +92,11 @@ export default class extends Endpoint { // eslint- .andWhere('clipNote.clipId = :clipId', { clipId: clip.id }); this.queryService.generateBlockedHostQueryForNote(query); + this.queryService.generateVisibilityQuery(query, me); if (me) { - this.queryService.generateVisibilityQuery(query, me); this.queryService.generateMutedUserQueryForNotes(query, me); this.queryService.generateBlockedUserQueryForNotes(query, me); + this.queryService.generateMutedUserRenotesQueryForNotes(query, me); } const notes = await query diff --git a/packages/backend/src/server/api/endpoints/notes/bubble-timeline.ts b/packages/backend/src/server/api/endpoints/notes/bubble-timeline.ts index df030d90aa..be08f84b6b 100644 --- a/packages/backend/src/server/api/endpoints/notes/bubble-timeline.ts +++ b/packages/backend/src/server/api/endpoints/notes/bubble-timeline.ts @@ -85,7 +85,7 @@ export default class extends Endpoint { // eslint- ps.sinceId, ps.untilId, ps.sinceDate, ps.untilDate) .andWhere('note.visibility = \'public\'') .andWhere('note.channelId IS NULL') - .andWhere('note.userHost IN (:...hosts)', { hosts: this.serverSettings.bubbleInstances }) + .andWhere('(note.userHost IS NULL OR userInstance.isBubbled = true)') // This comes from generateVisibilityQuery below .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') diff --git a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts index 91874a8195..5c1ab0fb78 100644 --- a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts +++ b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts @@ -96,10 +96,10 @@ export default class extends Endpoint { // eslint- if (!this.serverSettings.enableBotTrending) query.andWhere('user.isBot = FALSE'); - this.queryService.generateVisibilityQuery(query, me); - this.queryService.generateBlockedHostQueryForNote(query); + this.queryService.generateBlockedHostQueryForNote(query, undefined, false); if (me) this.queryService.generateMutedUserQueryForNotes(query, me); if (me) this.queryService.generateBlockedUserQueryForNotes(query, me); + if (me) this.queryService.generateMutedUserRenotesQueryForNotes(query, me); const followings = me ? await this.cacheService.userFollowingsCache.fetch(me.id) : {}; @@ -160,7 +160,6 @@ export default class extends Endpoint { // eslint- if (note.user?.isSuspended) return false; if (note.userHost) { if (!this.utilityService.isFederationAllowedHost(note.userHost)) return false; - if (this.utilityService.isSilencedHost(this.serverSettings.silencedHosts, note.userHost)) return false; } return true; }); diff --git a/packages/backend/src/server/api/endpoints/roles/notes.ts b/packages/backend/src/server/api/endpoints/roles/notes.ts index d1c2e4b686..536384a381 100644 --- a/packages/backend/src/server/api/endpoints/roles/notes.ts +++ b/packages/backend/src/server/api/endpoints/roles/notes.ts @@ -107,10 +107,10 @@ export default class extends Endpoint { // eslint- .leftJoinAndSelect('reply.user', 'replyUser') .leftJoinAndSelect('renote.user', 'renoteUser'); - this.queryService.generateVisibilityQuery(query, me); this.queryService.generateBlockedHostQueryForNote(query); this.queryService.generateMutedUserQueryForNotes(query, me); this.queryService.generateBlockedUserQueryForNotes(query, me); + this.queryService.generateMutedUserRenotesQueryForNotes(query, me); const notes = await query.getMany(); notes.sort((a, b) => a.id > b.id ? -1 : 1); diff --git a/packages/backend/src/server/api/endpoints/users/reactions.ts b/packages/backend/src/server/api/endpoints/users/reactions.ts index 56f59bd285..553787ad58 100644 --- a/packages/backend/src/server/api/endpoints/users/reactions.ts +++ b/packages/backend/src/server/api/endpoints/users/reactions.ts @@ -105,10 +105,15 @@ export default class extends Endpoint { // eslint- const query = this.queryService.makePaginationQuery(this.noteReactionsRepository.createQueryBuilder('reaction'), ps.sinceId, ps.untilId, ps.sinceDate, ps.untilDate) .andWhere('reaction.userId = :userId', { userId: ps.userId }) - .leftJoinAndSelect('reaction.note', 'note'); + .innerJoinAndSelect('reaction.note', 'note'); this.queryService.generateVisibilityQuery(query, me); this.queryService.generateBlockedHostQueryForNote(query); + if (me) { + this.queryService.generateMutedUserQueryForNotes(query, me); + this.queryService.generateBlockedUserQueryForNotes(query, me); + this.queryService.generateMutedUserRenotesQueryForNotes(query, me); + } const reactions = (await query .limit(ps.limit) -- cgit v1.2.3-freya From ac8fad242260a4f220278ff41e02d071b3db66ca Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Sun, 1 Jun 2025 14:34:58 -0400 Subject: fix logic error in generateBlockedHostQueryForNote that would allow blocked instances if allowSilenced was set to false --- packages/backend/src/core/QueryService.ts | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index 1b00f41d20..14d4caff06 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -263,8 +263,12 @@ export class QueryService { .orWhere(`note.${key}Host IS NULL`) // local .orWhere(`${key}Instance.isBlocked = false`); // not blocked - if (!allowSilenced) { - qb.orWhere(`${key}Instance.isSilenced = false`); // not silenced + if (allowSilenced) { + qb.orWhere(`${key}Instance.isBlocked = false`); // not blocked + } else { + qb.orWhere(new Brackets(qbb => qbb + .andWhere(`${key}Instance.isBlocked = false`) // not blocked + .andWhere(`${key}Instance.isSilenced = false`))); // not silenced } if (excludeAuthor) { -- cgit v1.2.3-freya From b7abc5b3b46f9399706d6def88cc6142265be37c Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Sun, 1 Jun 2025 14:42:13 -0400 Subject: fix withRenotes check in generateMutedUserRenotesQueryForNotes --- packages/backend/src/core/QueryService.ts | 1 + 1 file changed, 1 insertion(+) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index 1b00f41d20..51d3f1e2ea 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -247,6 +247,7 @@ export class QueryService { .orWhere('note.text IS NOT NULL') .orWhere('note.cw IS NOT NULL') .orWhere('note.replyId IS NOT NULL') + .orWhere('note.hasPoll = false') .orWhere('note.fileIds != \'{}\'') .orWhere(`note.userId NOT IN (${ mutingQuery.getQuery() })`); })); -- cgit v1.2.3-freya From a50a11fb4cf70b187421eefd738cf904c15b3f47 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Sun, 1 Jun 2025 15:25:54 -0400 Subject: remove duplicate isBlocked check from generateBlockedHostQueryForNote --- packages/backend/src/core/QueryService.ts | 3 +-- 1 file changed, 1 insertion(+), 2 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index 14d4caff06..5cec55412d 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -260,8 +260,7 @@ export class QueryService { q.leftJoin(`note.${key}Instance`, `${key}Instance`); q.andWhere(new Brackets(qb => { qb.orWhere(`note.${key}Id IS NULL`) // no corresponding user - .orWhere(`note.${key}Host IS NULL`) // local - .orWhere(`${key}Instance.isBlocked = false`); // not blocked + .orWhere(`note.${key}Host IS NULL`); // local if (allowSilenced) { qb.orWhere(`${key}Instance.isBlocked = false`); // not blocked -- cgit v1.2.3-freya From cbefbd2a33eefcada4be2554d3f2560ad094b5f5 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Mon, 2 Jun 2025 16:58:54 -0400 Subject: refactor QueryService to use EXISTS instead of IN for most queries --- packages/backend/src/core/QueryService.ts | 370 ++++++++++++++++++------------ 1 file changed, 218 insertions(+), 152 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index cf2419a9eb..548887959d 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -4,13 +4,13 @@ */ import { Inject, Injectable } from '@nestjs/common'; -import { Brackets, ObjectLiteral } from 'typeorm'; +import { Brackets, Not, WhereExpressionBuilder } from 'typeorm'; import { DI } from '@/di-symbols.js'; import type { MiUser } from '@/models/User.js'; -import type { UserProfilesRepository, FollowingsRepository, ChannelFollowingsRepository, BlockingsRepository, NoteThreadMutingsRepository, MutingsRepository, RenoteMutingsRepository, MiMeta } from '@/models/_.js'; +import type { UserProfilesRepository, FollowingsRepository, ChannelFollowingsRepository, BlockingsRepository, NoteThreadMutingsRepository, MutingsRepository, RenoteMutingsRepository, MiMeta, InstancesRepository, MiInstance } from '@/models/_.js'; import { bindThis } from '@/decorators.js'; import { IdService } from '@/core/IdService.js'; -import type { SelectQueryBuilder } from 'typeorm'; +import type { SelectQueryBuilder, FindOptionsWhere, ObjectLiteral } from 'typeorm'; @Injectable() export class QueryService { @@ -36,6 +36,9 @@ export class QueryService { @Inject(DI.renoteMutingsRepository) private renoteMutingsRepository: RenoteMutingsRepository, + @Inject(DI.instancesRepository) + private readonly instancesRepository: InstancesRepository, + @Inject(DI.meta) private meta: MiMeta, @@ -72,215 +75,278 @@ export class QueryService { // ここでいうBlockedは被Blockedの意 @bindThis - public generateBlockedUserQueryForNotes(q: SelectQueryBuilder, me: { id: MiUser['id'] }): void { - const blockingQuery = this.blockingsRepository.createQueryBuilder('blocking') - .select('blocking.blockerId') - .where('blocking.blockeeId = :blockeeId', { blockeeId: me.id }); - + public generateBlockedUserQueryForNotes(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { // 投稿の作者にブロックされていない かつ // 投稿の返信先の作者にブロックされていない かつ // 投稿の引用元の作者にブロックされていない - q - .andWhere(`note.userId NOT IN (${ blockingQuery.getQuery() })`) + return this.excludeBlockingUser(q, 'note.userId', ':meId') .andWhere(new Brackets(qb => { - qb - .where('note.replyUserId IS NULL') - .orWhere(`note.replyUserId NOT IN (${ blockingQuery.getQuery() })`); + this.excludeBlockingUser(qb, 'note.replyUserId', ':meId') + .orWhere('note.replyUserId IS NULL'); })) .andWhere(new Brackets(qb => { - qb - .where('note.renoteUserId IS NULL') - .orWhere(`note.renoteUserId NOT IN (${ blockingQuery.getQuery() })`); - })); - - q.setParameters(blockingQuery.getParameters()); + this.excludeBlockingUser(qb, 'note.renoteUserId', ':meId') + .orWhere('note.renoteUserId IS NULL'); + })) + .setParameters({ meId: me.id }); } @bindThis - public generateBlockQueryForUsers(q: SelectQueryBuilder, me: { id: MiUser['id'] }): void { - const blockingQuery = this.blockingsRepository.createQueryBuilder('blocking') - .select('blocking.blockeeId') - .where('blocking.blockerId = :blockerId', { blockerId: me.id }); - - const blockedQuery = this.blockingsRepository.createQueryBuilder('blocking') - .select('blocking.blockerId') - .where('blocking.blockeeId = :blockeeId', { blockeeId: me.id }); - - q.andWhere(`user.id NOT IN (${ blockingQuery.getQuery() })`); - q.setParameters(blockingQuery.getParameters()); - - q.andWhere(`user.id NOT IN (${ blockedQuery.getQuery() })`); - q.setParameters(blockedQuery.getParameters()); + public generateBlockQueryForUsers(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { + this.excludeBlockingUser(q, ':meId', 'user.id'); + this.excludeBlockingUser(q, 'user.id', ':me.id'); + return q.setParameters({ meId: me.id }); } @bindThis - public generateMutedNoteThreadQuery(q: SelectQueryBuilder, me: { id: MiUser['id'] }): void { - const mutedQuery = this.noteThreadMutingsRepository.createQueryBuilder('threadMuted') - .select('threadMuted.threadId') - .where('threadMuted.userId = :userId', { userId: me.id }); - - q.andWhere(`note.id NOT IN (${ mutedQuery.getQuery() })`); - q.andWhere(new Brackets(qb => { - qb - .where('note.threadId IS NULL') - .orWhere(`note.threadId NOT IN (${ mutedQuery.getQuery() })`); - })); - - q.setParameters(mutedQuery.getParameters()); + public generateMutedNoteThreadQuery(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { + return this.excludeMutingThread(q, ':meId', 'note.id') + .andWhere(new Brackets(qb => { + this.excludeMutingThread(qb, ':meId', 'note.threadId') + .orWhere('note.threadId IS NULL'); + })) + .setParameters({ meId: me.id }); } @bindThis - public generateMutedUserQueryForNotes(q: SelectQueryBuilder, me: { id: MiUser['id'] }, exclude?: { id: MiUser['id'] }): void { - const mutingQuery = this.mutingsRepository.createQueryBuilder('muting') - .select('muting.muteeId') - .where('muting.muterId = :muterId', { muterId: me.id }); - - if (exclude) { - mutingQuery.andWhere('muting.muteeId != :excludeId', { excludeId: exclude.id }); - } - - const mutingInstanceQuery = this.userProfilesRepository.createQueryBuilder('user_profile') - .select('user_profile.mutedInstances') - .where('user_profile.userId = :muterId', { muterId: me.id }); - + public generateMutedUserQueryForNotes(q: SelectQueryBuilder, me: { id: MiUser['id'] }, exclude?: { id: MiUser['id'] }): SelectQueryBuilder { // 投稿の作者をミュートしていない かつ // 投稿の返信先の作者をミュートしていない かつ // 投稿の引用元の作者をミュートしていない - q - .andWhere(`note.userId NOT IN (${ mutingQuery.getQuery() })`) + this.excludeMutingUser(q, ':meId', 'note.userId', exclude) .andWhere(new Brackets(qb => { - qb - .where('note.replyUserId IS NULL') - .orWhere(`note.replyUserId NOT IN (${ mutingQuery.getQuery() })`); - })) - .andWhere(new Brackets(qb => { - qb - .where('note.renoteUserId IS NULL') - .orWhere(`note.renoteUserId NOT IN (${ mutingQuery.getQuery() })`); + this.excludeMutingUser(qb, ':meId', 'note.replyUserId', exclude) + .orWhere('note.replyUserId IS NULL'); })) - // mute instances .andWhere(new Brackets(qb => { - qb - .andWhere('note.userHost IS NULL') - .orWhere(`NOT ((${ mutingInstanceQuery.getQuery() })::jsonb ? note.userHost)`); - })) + this.excludeMutingUser(qb, ':meId', 'note.renoteUserId', exclude) + .orWhere('note.renoteUserId IS NULL'); + })); + + // mute instances + this.excludeMutingInstance(q, ':meId', 'note.userHost') .andWhere(new Brackets(qb => { - qb - .where('note.replyUserHost IS NULL') - .orWhere(`NOT ((${ mutingInstanceQuery.getQuery() })::jsonb ? note.replyUserHost)`); + this.excludeMutingInstance(qb, ':meId', 'note.replyUserHost') + .orWhere('note.replyUserHost IS NULL'); })) .andWhere(new Brackets(qb => { - qb - .where('note.renoteUserHost IS NULL') - .orWhere(`NOT ((${ mutingInstanceQuery.getQuery() })::jsonb ? note.renoteUserHost)`); + this.excludeMutingInstance(qb, ':meId', 'note.renoteUserHost') + .orWhere('note.renoteUserHost IS NULL'); })); - q.setParameters(mutingQuery.getParameters()); - q.setParameters(mutingInstanceQuery.getParameters()); + return q.setParameters({ meId: me.id }); } @bindThis - public generateMutedUserQueryForUsers(q: SelectQueryBuilder, me: { id: MiUser['id'] }): void { - const mutingQuery = this.mutingsRepository.createQueryBuilder('muting') - .select('muting.muteeId') - .where('muting.muterId = :muterId', { muterId: me.id }); - - q.andWhere(`user.id NOT IN (${ mutingQuery.getQuery() })`); - - q.setParameters(mutingQuery.getParameters()); + public generateMutedUserQueryForUsers(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { + return this.excludeMutingUser(q, ':meId', 'user.id') + .setParameters({ meId: me.id }); } + // This intentionally skips isSuspended, isDeleted, makeNotesFollowersOnlyBefore, makeNotesHiddenBefore, and requireSigninToViewContents. + // NoteEntityService checks these automatically and calls hideNote() to hide them without breaking threads. + // For moderation purposes, you can set isSilenced to forcibly hide existing posts by a user. @bindThis - public generateVisibilityQuery(q: SelectQueryBuilder, me?: { id: MiUser['id'] } | null): void { + public generateVisibilityQuery(q: SelectQueryBuilder, me?: { id: MiUser['id'] } | null): SelectQueryBuilder { // This code must always be synchronized with the checks in Notes.isVisibleForMe. - if (me == null) { - q.andWhere(new Brackets(qb => { - qb - .where('note.visibility = \'public\'') - .orWhere('note.visibility = \'home\''); - })); - } else { - const followingQuery = this.followingsRepository.createQueryBuilder('following') - .select('following.followeeId') - .where('following.followerId = :meId'); + return q.andWhere(new Brackets(qb => { + // Public post + qb.orWhere('note.visibility = \'public\'') + .orWhere('note.visibility = \'home\''); - q.andWhere(new Brackets(qb => { + if (me != null) { qb - // 公開投稿である - .where(new Brackets(qb => { - qb - .where('note.visibility = \'public\'') - .orWhere('note.visibility = \'home\''); - })) - // または 自分自身 - .orWhere('note.userId = :meId') - // または 自分宛て - .orWhere(':meIdAsList <@ note.visibleUserIds') + // My post + .orWhere(':meId = note.userId') + // Reply to me + .orWhere(':meId = note.replyUserId') + // DM to me + .orWhere(':meId = ANY (note.visibleUserIds)') + // Mentions me + .orWhere(':meId = ANY (note.mentions)') + // Followers-only post .orWhere(new Brackets(qb => { - qb // または フォロワー宛ての投稿であり、 - .where('note.visibility = \'followers\'') - .andWhere(new Brackets(qb => { - qb - // 自分がフォロワーである - .where(`note.userId IN (${ followingQuery.getQuery() })`) - // または 自分の投稿へのリプライ - .orWhere('note.replyUserId = :meId') - .orWhere(':meIdAsList <@ note.mentions'); - })); + this.addFollowingUser(qb, ':meId', 'note.userId') + .andWhere('note.visibility = \'followers\''); })); - })); - q.setParameters({ meId: me.id, meIdAsList: [me.id] }); - } + q.setParameters({ meId: me.id }); + } + })); } @bindThis - public generateMutedUserRenotesQueryForNotes(q: SelectQueryBuilder, me: { id: MiUser['id'] }): void { - const mutingQuery = this.renoteMutingsRepository.createQueryBuilder('renote_muting') - .select('renote_muting.muteeId') - .where('renote_muting.muterId = :muterId', { muterId: me.id }); - - q.andWhere(new Brackets(qb => { - qb + public generateMutedUserRenotesQueryForNotes(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { + return q.andWhere(new Brackets(qb => { + this.excludeMutingRenote(qb, ':meId', 'note.userId') .orWhere('note.renoteId IS NULL') .orWhere('note.text IS NOT NULL') .orWhere('note.cw IS NOT NULL') .orWhere('note.replyId IS NOT NULL') - .orWhere('note.hasPoll = false') - .orWhere('note.fileIds != \'{}\'') - .orWhere(`note.userId NOT IN (${ mutingQuery.getQuery() })`); - })); - - q.setParameters(mutingQuery.getParameters()); + .orWhere('note.hasPoll = true') + .orWhere('note.fileIds != \'{}\''); + })) + .setParameters({ meId: me.id }); } + // TODO replace allowSilenced with matchingHostQuery @bindThis - public generateBlockedHostQueryForNote(q: SelectQueryBuilder, excludeAuthor?: boolean, allowSilenced = true): void { - function checkFor(key: 'user' | 'replyUser' | 'renoteUser') { - q.leftJoin(`note.${key}Instance`, `${key}Instance`); + public generateBlockedHostQueryForNote(q: SelectQueryBuilder, excludeAuthor?: boolean, allowSilenced = true): SelectQueryBuilder { + const checkFor = (key: 'user' | 'replyUser' | 'renoteUser') => { q.andWhere(new Brackets(qb => { - qb.orWhere(`note.${key}Id IS NULL`) // no corresponding user - .orWhere(`note.${key}Host IS NULL`); // local + qb.orWhere(`note.${key}Host IS NULL`); // local - if (allowSilenced) { - qb.orWhere(`${key}Instance.isBlocked = false`); // not blocked - } else { - qb.orWhere(new Brackets(qbb => qbb - .andWhere(`${key}Instance.isBlocked = false`) // not blocked - .andWhere(`${key}Instance.isSilenced = false`))); // not silenced + if (key !== 'user') { + // note.userId always exists and is non-null + qb.orWhere(`note.${key}Id IS NULL`); // no corresponding user + + // note.userId always equals note.userId + if (excludeAuthor) { + qb.orWhere(`note.userId = note.${key}Id`); // author + } } - if (excludeAuthor) { - qb.orWhere(`note.userId = note.${key}Id`); // author + if (allowSilenced) { + // not blocked + this.excludeInstanceWhere(qb, `note.${key}Host`, { + isBlocked: false, + }, 'orWhere'); + } else { + // not blocked or silenced + this.excludeInstanceWhere(qb, `note.${key}Host`, { + isBlocked: false, + isSilenced: false, + }, 'orWhere'); } })); - } + }; if (!excludeAuthor) { checkFor('user'); } checkFor('replyUser'); checkFor('renoteUser'); + + return q; + } + + @bindThis + public generateMatchingHostQueryForNote(q: SelectQueryBuilder, filters: FindOptionsWhere | FindOptionsWhere[], hostProp = 'note.userHost'): SelectQueryBuilder { + return this.includeInstanceWhere(q, hostProp, filters); + } + + /** + * Adds condition that hostProp (instance host) matches the given filters. + * The prop should be an expression, not raw values. + */ + @bindThis + public includeInstanceWhere(q: Q, hostProp: string, filters: FindOptionsWhere | FindOptionsWhere[], join: 'andWhere' | 'orWhere' = 'andWhere'): Q { + const instancesQuery = this.instancesRepository.createQueryBuilder('instance') + .select('1') + .andWhere(`instance.host = ${hostProp}`) + .andWhere(filters); + + return q[join](`EXISTS (${instancesQuery.getQuery()})`, instancesQuery.getParameters()); + } + + /** + * Adds condition that hostProp (instance host) matches the given filters. + * The prop should be an expression, not raw values. + */ + @bindThis + public excludeInstanceWhere(q: Q, hostProp: string, filters: FindOptionsWhere | FindOptionsWhere[], join: 'andWhere' | 'orWhere' = 'andWhere'): Q { + const instancesQuery = this.instancesRepository.createQueryBuilder('instance') + .select('1') + .andWhere(`instance.host = ${hostProp}`) + .andWhere(filters); + + return q[join](`NOT EXISTS (${instancesQuery.getQuery()})`, instancesQuery.getParameters()); + } + + /** + * Adds condition that followerProp (user ID) is following followeeProp (user ID). + * Both props should be expressions, not raw values. + */ + public addFollowingUser(q: Q, followerProp: string, followeeProp: string): Q { + const followingQuery = this.followingsRepository.createQueryBuilder('following') + .select('1') + .andWhere(`following.followerId = ${followerProp}`) + .andWhere(`following.followeeId = ${followeeProp}`); + + return q.andWhere(`EXISTS (${followingQuery.getQuery()})`, followingQuery.getParameters()); + }; + + /** + * Adds condition that blockerProp (user ID) is not blocking blockeeProp (user ID). + * Both props should be expressions, not raw values. + */ + @bindThis + public excludeBlockingUser(q: Q, blockerProp: string, blockeeProp: string): Q { + const blockingQuery = this.blockingsRepository.createQueryBuilder('blocking') + .select('1') + .andWhere(`blocking.blockerId = ${blockerProp}`) + .andWhere(`blocking.blockeeId = ${blockeeProp}`); + + return q.andWhere(`NOT EXISTS (${blockingQuery.getQuery()})`, blockingQuery.getParameters()); + }; + + /** + * Adds condition that muterProp (user ID) is not muting muteeProp (user ID). + * Both props should be expressions, not raw values. + */ + @bindThis + public excludeMutingUser(q: Q, muterProp: string, muteeProp: string, exclude?: { id: MiUser['id'] }): Q { + const mutingQuery = this.mutingsRepository.createQueryBuilder('muting') + .select('1') + .andWhere(`muting.muterId = ${muterProp}`) + .andWhere(`muting.muteeId = ${muteeProp}`); + + if (exclude) { + mutingQuery.andWhere({ muteeId: Not(exclude.id) }); + } + + return q.andWhere(`NOT EXISTS (${mutingQuery.getQuery()})`, mutingQuery.getParameters()); + } + + /** + * Adds condition that muterProp (user ID) is not muting renotes by muteeProp (user ID). + * Both props should be expressions, not raw values. + */ + public excludeMutingRenote(q: Q, muterProp: string, muteeProp: string): Q { + const mutingQuery = this.renoteMutingsRepository.createQueryBuilder('renote_muting') + .select('1') + .andWhere(`renote_muting.muterId = ${muterProp}`) + .andWhere(`renote_muting.muteeId = ${muteeProp}`); + + return q.andWhere(`NOT EXISTS (${mutingQuery.getQuery()})`, mutingQuery.getParameters()); + }; + + /** + * Adds condition that muterProp (user ID) is not muting muteeProp (instance host). + * Both props should be expressions, not raw values. + */ + @bindThis + public excludeMutingInstance(q: Q, muterProp: string, muteeProp: string): Q { + const mutingInstanceQuery = this.userProfilesRepository.createQueryBuilder('user_profile') + .select('1') + .andWhere(`user_profile.userId = ${muterProp}`) + .andWhere(`"user_profile"."mutedInstances"::jsonb ? ${muteeProp}`); + + return q.andWhere(`NOT EXISTS (${mutingInstanceQuery.getQuery()})`, mutingInstanceQuery.getParameters()); + } + + /** + * Adds condition that muterProp (user ID) is not muting muteeProp (note ID). + * Both props should be expressions, not raw values. + */ + @bindThis + public excludeMutingThread(q: Q, muterProp: string, muteeProp: string): Q { + const threadMutedQuery = this.noteThreadMutingsRepository.createQueryBuilder('threadMuted') + .select('1') + .andWhere(`threadMuted.userId = ${muterProp}`) + .andWhere(`threadMuted.threadId = ${muteeProp}`); + + return q.andWhere(`NOT EXISTS (${threadMutedQuery.getQuery()})`, threadMutedQuery.getParameters()); } } -- cgit v1.2.3-freya From 0b9c0a6bc7f8bcd4ef1d3025884487467df81598 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Mon, 2 Jun 2025 22:51:10 -0400 Subject: fix andWhere/orWhere in QueryService.ts --- packages/backend/src/core/QueryService.ts | 61 ++++++++++++++++++++----------- 1 file changed, 40 insertions(+), 21 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index 548887959d..0f74e7cab1 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -81,11 +81,11 @@ export class QueryService { // 投稿の引用元の作者にブロックされていない return this.excludeBlockingUser(q, 'note.userId', ':meId') .andWhere(new Brackets(qb => { - this.excludeBlockingUser(qb, 'note.replyUserId', ':meId') + this.excludeBlockingUser(qb, 'note.replyUserId', ':meId', 'orWhere') .orWhere('note.replyUserId IS NULL'); })) .andWhere(new Brackets(qb => { - this.excludeBlockingUser(qb, 'note.renoteUserId', ':meId') + this.excludeBlockingUser(qb, 'note.renoteUserId', ':meId', 'orWhere') .orWhere('note.renoteUserId IS NULL'); })) .setParameters({ meId: me.id }); @@ -102,7 +102,7 @@ export class QueryService { public generateMutedNoteThreadQuery(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { return this.excludeMutingThread(q, ':meId', 'note.id') .andWhere(new Brackets(qb => { - this.excludeMutingThread(qb, ':meId', 'note.threadId') + this.excludeMutingThread(qb, ':meId', 'note.threadId', 'orWhere') .orWhere('note.threadId IS NULL'); })) .setParameters({ meId: me.id }); @@ -113,24 +113,24 @@ export class QueryService { // 投稿の作者をミュートしていない かつ // 投稿の返信先の作者をミュートしていない かつ // 投稿の引用元の作者をミュートしていない - this.excludeMutingUser(q, ':meId', 'note.userId', exclude) + this.excludeMutingUser(q, ':meId', 'note.userId', 'andWhere', exclude) .andWhere(new Brackets(qb => { - this.excludeMutingUser(qb, ':meId', 'note.replyUserId', exclude) + this.excludeMutingUser(qb, ':meId', 'note.replyUserId', 'orWhere', exclude) .orWhere('note.replyUserId IS NULL'); })) .andWhere(new Brackets(qb => { - this.excludeMutingUser(qb, ':meId', 'note.renoteUserId', exclude) + this.excludeMutingUser(qb, ':meId', 'note.renoteUserId', 'orWhere', exclude) .orWhere('note.renoteUserId IS NULL'); })); // mute instances - this.excludeMutingInstance(q, ':meId', 'note.userHost') + this.excludeMutingInstance(q, ':meId', 'note.userHost', 'andWhere') .andWhere(new Brackets(qb => { - this.excludeMutingInstance(qb, ':meId', 'note.replyUserHost') + this.excludeMutingInstance(qb, ':meId', 'note.replyUserHost', 'orWhere') .orWhere('note.replyUserHost IS NULL'); })) .andWhere(new Brackets(qb => { - this.excludeMutingInstance(qb, ':meId', 'note.renoteUserHost') + this.excludeMutingInstance(qb, ':meId', 'note.renoteUserHost', 'orWhere') .orWhere('note.renoteUserHost IS NULL'); })); @@ -231,6 +231,25 @@ export class QueryService { return q; } + @bindThis + public generateSilencedUserQueryForNotes(q: SelectQueryBuilder, me?: { id: MiUser['id'] } | null, userProp = 'user'): SelectQueryBuilder { + if (!me) { + return q.andWhere(`${userProp}.isSilenced = false`); + } + + return q + .andWhere(new Brackets(qb => { + // case 1: we are following the user + this.addFollowingUser(qb, ':meId', `${userProp}.id`, 'orWhere'); + // case 2: user not silenced AND instance not silenced + qb.orWhere(new Brackets(qbb => { + this.includeInstanceWhere(qbb, `${userProp}.host`, { isSilenced: false }); + qbb.andWhere(`${userProp}.isSilenced = false`); + })); + })) + .setParameters({ meId: me.id }); + } + @bindThis public generateMatchingHostQueryForNote(q: SelectQueryBuilder, filters: FindOptionsWhere | FindOptionsWhere[], hostProp = 'note.userHost'): SelectQueryBuilder { return this.includeInstanceWhere(q, hostProp, filters); @@ -268,13 +287,13 @@ export class QueryService { * Adds condition that followerProp (user ID) is following followeeProp (user ID). * Both props should be expressions, not raw values. */ - public addFollowingUser(q: Q, followerProp: string, followeeProp: string): Q { + public addFollowingUser(q: Q, followerProp: string, followeeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { const followingQuery = this.followingsRepository.createQueryBuilder('following') .select('1') .andWhere(`following.followerId = ${followerProp}`) .andWhere(`following.followeeId = ${followeeProp}`); - return q.andWhere(`EXISTS (${followingQuery.getQuery()})`, followingQuery.getParameters()); + return q[join](`EXISTS (${followingQuery.getQuery()})`, followingQuery.getParameters()); }; /** @@ -282,13 +301,13 @@ export class QueryService { * Both props should be expressions, not raw values. */ @bindThis - public excludeBlockingUser(q: Q, blockerProp: string, blockeeProp: string): Q { + public excludeBlockingUser(q: Q, blockerProp: string, blockeeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { const blockingQuery = this.blockingsRepository.createQueryBuilder('blocking') .select('1') .andWhere(`blocking.blockerId = ${blockerProp}`) .andWhere(`blocking.blockeeId = ${blockeeProp}`); - return q.andWhere(`NOT EXISTS (${blockingQuery.getQuery()})`, blockingQuery.getParameters()); + return q[join](`NOT EXISTS (${blockingQuery.getQuery()})`, blockingQuery.getParameters()); }; /** @@ -296,7 +315,7 @@ export class QueryService { * Both props should be expressions, not raw values. */ @bindThis - public excludeMutingUser(q: Q, muterProp: string, muteeProp: string, exclude?: { id: MiUser['id'] }): Q { + public excludeMutingUser(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere', exclude?: { id: MiUser['id'] }): Q { const mutingQuery = this.mutingsRepository.createQueryBuilder('muting') .select('1') .andWhere(`muting.muterId = ${muterProp}`) @@ -306,20 +325,20 @@ export class QueryService { mutingQuery.andWhere({ muteeId: Not(exclude.id) }); } - return q.andWhere(`NOT EXISTS (${mutingQuery.getQuery()})`, mutingQuery.getParameters()); + return q[join](`NOT EXISTS (${mutingQuery.getQuery()})`, mutingQuery.getParameters()); } /** * Adds condition that muterProp (user ID) is not muting renotes by muteeProp (user ID). * Both props should be expressions, not raw values. */ - public excludeMutingRenote(q: Q, muterProp: string, muteeProp: string): Q { + public excludeMutingRenote(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { const mutingQuery = this.renoteMutingsRepository.createQueryBuilder('renote_muting') .select('1') .andWhere(`renote_muting.muterId = ${muterProp}`) .andWhere(`renote_muting.muteeId = ${muteeProp}`); - return q.andWhere(`NOT EXISTS (${mutingQuery.getQuery()})`, mutingQuery.getParameters()); + return q[join](`NOT EXISTS (${mutingQuery.getQuery()})`, mutingQuery.getParameters()); }; /** @@ -327,13 +346,13 @@ export class QueryService { * Both props should be expressions, not raw values. */ @bindThis - public excludeMutingInstance(q: Q, muterProp: string, muteeProp: string): Q { + public excludeMutingInstance(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { const mutingInstanceQuery = this.userProfilesRepository.createQueryBuilder('user_profile') .select('1') .andWhere(`user_profile.userId = ${muterProp}`) .andWhere(`"user_profile"."mutedInstances"::jsonb ? ${muteeProp}`); - return q.andWhere(`NOT EXISTS (${mutingInstanceQuery.getQuery()})`, mutingInstanceQuery.getParameters()); + return q[join](`NOT EXISTS (${mutingInstanceQuery.getQuery()})`, mutingInstanceQuery.getParameters()); } /** @@ -341,12 +360,12 @@ export class QueryService { * Both props should be expressions, not raw values. */ @bindThis - public excludeMutingThread(q: Q, muterProp: string, muteeProp: string): Q { + public excludeMutingThread(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { const threadMutedQuery = this.noteThreadMutingsRepository.createQueryBuilder('threadMuted') .select('1') .andWhere(`threadMuted.userId = ${muterProp}`) .andWhere(`threadMuted.threadId = ${muteeProp}`); - return q.andWhere(`NOT EXISTS (${threadMutedQuery.getQuery()})`, threadMutedQuery.getParameters()); + return q[join](`NOT EXISTS (${threadMutedQuery.getQuery()})`, threadMutedQuery.getParameters()); } } -- cgit v1.2.3-freya From 7ab5ce1537feb47480a47e4f3faab16d089820f1 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Mon, 2 Jun 2025 22:56:35 -0400 Subject: replace generateBlockedHostQueryForNote allowSilenced parameter with generateSilencedUserQueryForNotes --- packages/backend/src/core/QueryService.ts | 17 +++-------------- .../src/server/api/endpoints/notes/search-by-tag.ts | 3 ++- 2 files changed, 5 insertions(+), 15 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index 0f74e7cab1..2e0a368bd7 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -190,9 +190,8 @@ export class QueryService { .setParameters({ meId: me.id }); } - // TODO replace allowSilenced with matchingHostQuery @bindThis - public generateBlockedHostQueryForNote(q: SelectQueryBuilder, excludeAuthor?: boolean, allowSilenced = true): SelectQueryBuilder { + public generateBlockedHostQueryForNote(q: SelectQueryBuilder, excludeAuthor?: boolean): SelectQueryBuilder { const checkFor = (key: 'user' | 'replyUser' | 'renoteUser') => { q.andWhere(new Brackets(qb => { qb.orWhere(`note.${key}Host IS NULL`); // local @@ -207,18 +206,8 @@ export class QueryService { } } - if (allowSilenced) { - // not blocked - this.excludeInstanceWhere(qb, `note.${key}Host`, { - isBlocked: false, - }, 'orWhere'); - } else { - // not blocked or silenced - this.excludeInstanceWhere(qb, `note.${key}Host`, { - isBlocked: false, - isSilenced: false, - }, 'orWhere'); - } + // not blocked + this.excludeInstanceWhere(qb, `note.${key}Host`, { isBlocked: false }, 'orWhere'); })); }; diff --git a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts index 5c1ab0fb78..01bedd9b1d 100644 --- a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts +++ b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts @@ -96,7 +96,8 @@ export default class extends Endpoint { // eslint- if (!this.serverSettings.enableBotTrending) query.andWhere('user.isBot = FALSE'); - this.queryService.generateBlockedHostQueryForNote(query, undefined, false); + this.queryService.generateBlockedHostQueryForNote(query); + this.queryService.generateSilencedUserQueryForNotes(query, me); if (me) this.queryService.generateMutedUserQueryForNotes(query, me); if (me) this.queryService.generateBlockedUserQueryForNotes(query, me); if (me) this.queryService.generateMutedUserRenotesQueryForNotes(query, me); -- cgit v1.2.3-freya From 15ebb0ef85f2db2dad0fad4bf0c9b06f0daf1339 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Tue, 3 Jun 2025 15:12:59 -0400 Subject: more QueryService fixes --- packages/backend/src/core/QueryService.ts | 284 ++++++++++++++++++------------ 1 file changed, 174 insertions(+), 110 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index 2e0a368bd7..bf5b0b359f 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -7,10 +7,11 @@ import { Inject, Injectable } from '@nestjs/common'; import { Brackets, Not, WhereExpressionBuilder } from 'typeorm'; import { DI } from '@/di-symbols.js'; import type { MiUser } from '@/models/User.js'; -import type { UserProfilesRepository, FollowingsRepository, ChannelFollowingsRepository, BlockingsRepository, NoteThreadMutingsRepository, MutingsRepository, RenoteMutingsRepository, MiMeta, InstancesRepository, MiInstance } from '@/models/_.js'; +import { MiInstance } from '@/models/Instance.js'; +import type { UserProfilesRepository, FollowingsRepository, ChannelFollowingsRepository, BlockingsRepository, NoteThreadMutingsRepository, MutingsRepository, RenoteMutingsRepository, MiMeta, InstancesRepository } from '@/models/_.js'; import { bindThis } from '@/decorators.js'; import { IdService } from '@/core/IdService.js'; -import type { SelectQueryBuilder, FindOptionsWhere, ObjectLiteral } from 'typeorm'; +import type { SelectQueryBuilder, ObjectLiteral } from 'typeorm'; @Injectable() export class QueryService { @@ -79,32 +80,31 @@ export class QueryService { // 投稿の作者にブロックされていない かつ // 投稿の返信先の作者にブロックされていない かつ // 投稿の引用元の作者にブロックされていない - return this.excludeBlockingUser(q, 'note.userId', ':meId') - .andWhere(new Brackets(qb => { - this.excludeBlockingUser(qb, 'note.replyUserId', ':meId', 'orWhere') - .orWhere('note.replyUserId IS NULL'); - })) - .andWhere(new Brackets(qb => { - this.excludeBlockingUser(qb, 'note.renoteUserId', ':meId', 'orWhere') - .orWhere('note.renoteUserId IS NULL'); - })) + return this + .andNotBlockingUser(q, 'note.userId', ':meId') + .andWhere(new Brackets(qb => this + .orNotBlockingUser(qb, 'note.replyUserId', ':meId') + .orWhere('note.replyUserId IS NULL'))) + .andWhere(new Brackets(qb => this + .orNotBlockingUser(qb, 'note.renoteUserId', ':meId') + .orWhere('note.renoteUserId IS NULL'))) .setParameters({ meId: me.id }); } @bindThis public generateBlockQueryForUsers(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { - this.excludeBlockingUser(q, ':meId', 'user.id'); - this.excludeBlockingUser(q, 'user.id', ':me.id'); + this.andNotBlockingUser(q, ':meId', 'user.id'); + this.andNotBlockingUser(q, 'user.id', ':me.id'); return q.setParameters({ meId: me.id }); } @bindThis public generateMutedNoteThreadQuery(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { - return this.excludeMutingThread(q, ':meId', 'note.id') - .andWhere(new Brackets(qb => { - this.excludeMutingThread(qb, ':meId', 'note.threadId', 'orWhere') - .orWhere('note.threadId IS NULL'); - })) + return this + .andNotMutingThread(q, ':meId', 'note.id') + .andWhere(new Brackets(qb => this + .orNotMutingThread(qb, ':meId', 'note.threadId') + .orWhere('note.threadId IS NULL'))) .setParameters({ meId: me.id }); } @@ -113,33 +113,32 @@ export class QueryService { // 投稿の作者をミュートしていない かつ // 投稿の返信先の作者をミュートしていない かつ // 投稿の引用元の作者をミュートしていない - this.excludeMutingUser(q, ':meId', 'note.userId', 'andWhere', exclude) - .andWhere(new Brackets(qb => { - this.excludeMutingUser(qb, ':meId', 'note.replyUserId', 'orWhere', exclude) - .orWhere('note.replyUserId IS NULL'); - })) - .andWhere(new Brackets(qb => { - this.excludeMutingUser(qb, ':meId', 'note.renoteUserId', 'orWhere', exclude) - .orWhere('note.renoteUserId IS NULL'); - })); - - // mute instances - this.excludeMutingInstance(q, ':meId', 'note.userHost', 'andWhere') - .andWhere(new Brackets(qb => { - this.excludeMutingInstance(qb, ':meId', 'note.replyUserHost', 'orWhere') - .orWhere('note.replyUserHost IS NULL'); - })) - .andWhere(new Brackets(qb => { - this.excludeMutingInstance(qb, ':meId', 'note.renoteUserHost', 'orWhere') - .orWhere('note.renoteUserHost IS NULL'); - })); - - return q.setParameters({ meId: me.id }); + return this + .andNotMutingUser(q, ':meId', 'note.userId', exclude) + .andWhere(new Brackets(qb => this + .orNotMutingUser(qb, ':meId', 'note.replyUserId', exclude) + .orWhere('note.replyUserId IS NULL'))) + .andWhere(new Brackets(qb => this + .orNotMutingUser(qb, ':meId', 'note.renoteUserId', exclude) + .orWhere('note.renoteUserId IS NULL'))) + // TODO exclude should also pass a host to skip these instances + // mute instances + .andWhere(new Brackets(qb => this + .andNotMutingInstance(qb, ':meId', 'note.userHost') + .orWhere('note.userHost IS NULL'))) + .andWhere(new Brackets(qb => this + .orNotMutingInstance(qb, ':meId', 'note.replyUserHost') + .orWhere('note.replyUserHost IS NULL'))) + .andWhere(new Brackets(qb => this + .orNotMutingInstance(qb, ':meId', 'note.renoteUserHost') + .orWhere('note.renoteUserHost IS NULL'))) + .setParameters({ meId: me.id }); } @bindThis public generateMutedUserQueryForUsers(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { - return this.excludeMutingUser(q, ':meId', 'user.id') + return this + .andNotMutingUser(q, ':meId', 'user.id') .setParameters({ meId: me.id }); } @@ -165,11 +164,9 @@ export class QueryService { // Mentions me .orWhere(':meId = ANY (note.mentions)') // Followers-only post - .orWhere(new Brackets(qb => { - // または フォロワー宛ての投稿であり、 - this.addFollowingUser(qb, ':meId', 'note.userId') - .andWhere('note.visibility = \'followers\''); - })); + .orWhere(new Brackets(qb => this + .andFollowingUser(qb, ':meId', 'note.userId') + .andWhere('note.visibility = \'followers\''))); q.setParameters({ meId: me.id }); } @@ -178,38 +175,43 @@ export class QueryService { @bindThis public generateMutedUserRenotesQueryForNotes(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { - return q.andWhere(new Brackets(qb => { - this.excludeMutingRenote(qb, ':meId', 'note.userId') + return q + .andWhere(new Brackets(qb => this + .orNotMutingRenote(qb, ':meId', 'note.userId') .orWhere('note.renoteId IS NULL') .orWhere('note.text IS NOT NULL') .orWhere('note.cw IS NOT NULL') .orWhere('note.replyId IS NOT NULL') .orWhere('note.hasPoll = true') - .orWhere('note.fileIds != \'{}\''); - })) + .orWhere('note.fileIds != \'{}\''))) .setParameters({ meId: me.id }); } + @bindThis + public generateExcludedRenotesQueryForNotes(q: SelectQueryBuilder): SelectQueryBuilder { + return q + .andWhere(new Brackets(qb => qb + .orWhere('note.renoteId IS NULL') + .orWhere('note.text IS NOT NULL') + .orWhere('note.cw IS NOT NULL') + .orWhere('note.replyId IS NOT NULL') + .orWhere('note.hasPoll = true') + .orWhere('note.fileIds != \'{}\''))); + } + @bindThis public generateBlockedHostQueryForNote(q: SelectQueryBuilder, excludeAuthor?: boolean): SelectQueryBuilder { - const checkFor = (key: 'user' | 'replyUser' | 'renoteUser') => { - q.andWhere(new Brackets(qb => { - qb.orWhere(`note.${key}Host IS NULL`); // local - - if (key !== 'user') { - // note.userId always exists and is non-null - qb.orWhere(`note.${key}Id IS NULL`); // no corresponding user - - // note.userId always equals note.userId - if (excludeAuthor) { - qb.orWhere(`note.userId = note.${key}Id`); // author - } - } + const checkFor = (key: 'user' | 'replyUser' | 'renoteUser') => this + .leftJoinInstance(q, `note.${key}Instance`, `${key}Instance`) + .andWhere(new Brackets(qb => { + qb + .orWhere(`"${key}Instance" IS NULL`) // local + .orWhere(`"${key}Instance"."isBlocked" = false`); // not blocked - // not blocked - this.excludeInstanceWhere(qb, `note.${key}Host`, { isBlocked: false }, 'orWhere'); + if (excludeAuthor) { + qb.orWhere(`note.userId = note.${key}Id`); // author + } })); - }; if (!excludeAuthor) { checkFor('user'); @@ -221,62 +223,58 @@ export class QueryService { } @bindThis - public generateSilencedUserQueryForNotes(q: SelectQueryBuilder, me?: { id: MiUser['id'] } | null, userProp = 'user'): SelectQueryBuilder { + public generateSilencedUserQueryForNotes(q: SelectQueryBuilder, me?: { id: MiUser['id'] } | null): SelectQueryBuilder { if (!me) { - return q.andWhere(`${userProp}.isSilenced = false`); + return q.andWhere('user.isSilenced = false'); } - return q - .andWhere(new Brackets(qb => { + return this + .leftJoinInstance(q, 'note.userInstance', 'userInstance') + .andWhere(new Brackets(qb => this // case 1: we are following the user - this.addFollowingUser(qb, ':meId', `${userProp}.id`, 'orWhere'); + .orFollowingUser(qb, ':meId', 'note.userId') // case 2: user not silenced AND instance not silenced - qb.orWhere(new Brackets(qbb => { - this.includeInstanceWhere(qbb, `${userProp}.host`, { isSilenced: false }); - qbb.andWhere(`${userProp}.isSilenced = false`); - })); - })) + .orWhere(new Brackets(qbb => qbb + .andWhere(new Brackets(qbbb => qbbb + .orWhere('"userInstance"."isSilenced" = false') + .orWhere('"userInstance" IS NULL'))) + .andWhere('user.isSilenced = false'))))) .setParameters({ meId: me.id }); } - @bindThis - public generateMatchingHostQueryForNote(q: SelectQueryBuilder, filters: FindOptionsWhere | FindOptionsWhere[], hostProp = 'note.userHost'): SelectQueryBuilder { - return this.includeInstanceWhere(q, hostProp, filters); - } - /** - * Adds condition that hostProp (instance host) matches the given filters. - * The prop should be an expression, not raw values. + * Left-joins an instance in to the query with a given alias and optional condition. + * These calls are de-duplicated - multiple uses of the same alias are skipped. */ @bindThis - public includeInstanceWhere(q: Q, hostProp: string, filters: FindOptionsWhere | FindOptionsWhere[], join: 'andWhere' | 'orWhere' = 'andWhere'): Q { - const instancesQuery = this.instancesRepository.createQueryBuilder('instance') - .select('1') - .andWhere(`instance.host = ${hostProp}`) - .andWhere(filters); + public leftJoinInstance(q: SelectQueryBuilder, relation: string | typeof MiInstance, alias: string, condition?: string): SelectQueryBuilder { + // Skip if it's already joined, otherwise we'll get an error + if (!q.expressionMap.joinAttributes.some(j => j.alias.name === alias)) { + q.leftJoin(relation, alias, condition); + } - return q[join](`EXISTS (${instancesQuery.getQuery()})`, instancesQuery.getParameters()); + return q; } /** - * Adds condition that hostProp (instance host) matches the given filters. - * The prop should be an expression, not raw values. + * Adds OR condition that followerProp (user ID) is following followeeProp (user ID). + * Both props should be expressions, not raw values. */ @bindThis - public excludeInstanceWhere(q: Q, hostProp: string, filters: FindOptionsWhere | FindOptionsWhere[], join: 'andWhere' | 'orWhere' = 'andWhere'): Q { - const instancesQuery = this.instancesRepository.createQueryBuilder('instance') - .select('1') - .andWhere(`instance.host = ${hostProp}`) - .andWhere(filters); - - return q[join](`NOT EXISTS (${instancesQuery.getQuery()})`, instancesQuery.getParameters()); + public orFollowingUser(q: Q, followerProp: string, followeeProp: string): Q { + return this.addFollowingUser(q, followerProp, followeeProp, 'orWhere'); } /** - * Adds condition that followerProp (user ID) is following followeeProp (user ID). + * Adds AND condition that followerProp (user ID) is following followeeProp (user ID). * Both props should be expressions, not raw values. */ - public addFollowingUser(q: Q, followerProp: string, followeeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { + @bindThis + public andFollowingUser(q: Q, followerProp: string, followeeProp: string): Q { + return this.addFollowingUser(q, followerProp, followeeProp, 'andWhere'); + } + + private addFollowingUser(q: Q, followerProp: string, followeeProp: string, join: 'andWhere' | 'orWhere'): Q { const followingQuery = this.followingsRepository.createQueryBuilder('following') .select('1') .andWhere(`following.followerId = ${followerProp}`) @@ -286,11 +284,24 @@ export class QueryService { }; /** - * Adds condition that blockerProp (user ID) is not blocking blockeeProp (user ID). + * Adds OR condition that blockerProp (user ID) is not blocking blockeeProp (user ID). * Both props should be expressions, not raw values. */ @bindThis - public excludeBlockingUser(q: Q, blockerProp: string, blockeeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { + public orNotBlockingUser(q: Q, blockerProp: string, blockeeProp: string): Q { + return this.excludeBlockingUser(q, blockerProp, blockeeProp, 'orWhere'); + } + + /** + * Adds AND condition that blockerProp (user ID) is not blocking blockeeProp (user ID). + * Both props should be expressions, not raw values. + */ + @bindThis + public andNotBlockingUser(q: Q, blockerProp: string, blockeeProp: string): Q { + return this.excludeBlockingUser(q, blockerProp, blockeeProp, 'andWhere'); + } + + private excludeBlockingUser(q: Q, blockerProp: string, blockeeProp: string, join: 'andWhere' | 'orWhere'): Q { const blockingQuery = this.blockingsRepository.createQueryBuilder('blocking') .select('1') .andWhere(`blocking.blockerId = ${blockerProp}`) @@ -300,11 +311,24 @@ export class QueryService { }; /** - * Adds condition that muterProp (user ID) is not muting muteeProp (user ID). + * Adds OR condition that muterProp (user ID) is not muting muteeProp (user ID). * Both props should be expressions, not raw values. */ @bindThis - public excludeMutingUser(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere', exclude?: { id: MiUser['id'] }): Q { + public orNotMutingUser(q: Q, muterProp: string, muteeProp: string, exclude?: { id: MiUser['id'] }): Q { + return this.excludeMutingUser(q, muterProp, muteeProp, 'orWhere', exclude); + } + + /** + * Adds AND condition that muterProp (user ID) is not muting muteeProp (user ID). + * Both props should be expressions, not raw values. + */ + @bindThis + public andNotMutingUser(q: Q, muterProp: string, muteeProp: string, exclude?: { id: MiUser['id'] }): Q { + return this.excludeMutingUser(q, muterProp, muteeProp, 'andWhere', exclude); + } + + private excludeMutingUser(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere', exclude?: { id: MiUser['id'] }): Q { const mutingQuery = this.mutingsRepository.createQueryBuilder('muting') .select('1') .andWhere(`muting.muterId = ${muterProp}`) @@ -318,10 +342,24 @@ export class QueryService { } /** - * Adds condition that muterProp (user ID) is not muting renotes by muteeProp (user ID). + * Adds OR condition that muterProp (user ID) is not muting renotes by muteeProp (user ID). * Both props should be expressions, not raw values. */ - public excludeMutingRenote(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { + @bindThis + public orNotMutingRenote(q: Q, muterProp: string, muteeProp: string): Q { + return this.excludeMutingRenote(q, muterProp, muteeProp, 'orWhere'); + } + + /** + * Adds AND condition that muterProp (user ID) is not muting renotes by muteeProp (user ID). + * Both props should be expressions, not raw values. + */ + @bindThis + public andNotMutingRenote(q: Q, muterProp: string, muteeProp: string): Q { + return this.excludeMutingRenote(q, muterProp, muteeProp, 'andWhere'); + } + + private excludeMutingRenote(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere'): Q { const mutingQuery = this.renoteMutingsRepository.createQueryBuilder('renote_muting') .select('1') .andWhere(`renote_muting.muterId = ${muterProp}`) @@ -331,11 +369,24 @@ export class QueryService { }; /** - * Adds condition that muterProp (user ID) is not muting muteeProp (instance host). + * Adds OR condition that muterProp (user ID) is not muting muteeProp (instance host). + * Both props should be expressions, not raw values. + */ + @bindThis + public orNotMutingInstance(q: Q, muterProp: string, muteeProp: string): Q { + return this.excludeMutingInstance(q, muterProp, muteeProp, 'orWhere'); + } + + /** + * Adds AND condition that muterProp (user ID) is not muting muteeProp (instance host). * Both props should be expressions, not raw values. */ @bindThis - public excludeMutingInstance(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { + public andNotMutingInstance(q: Q, muterProp: string, muteeProp: string): Q { + return this.excludeMutingInstance(q, muterProp, muteeProp, 'andWhere'); + } + + private excludeMutingInstance(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere'): Q { const mutingInstanceQuery = this.userProfilesRepository.createQueryBuilder('user_profile') .select('1') .andWhere(`user_profile.userId = ${muterProp}`) @@ -345,11 +396,24 @@ export class QueryService { } /** - * Adds condition that muterProp (user ID) is not muting muteeProp (note ID). + * Adds OR condition that muterProp (user ID) is not muting muteeProp (note ID). + * Both props should be expressions, not raw values. + */ + @bindThis + public orNotMutingThread(q: Q, muterProp: string, muteeProp: string): Q { + return this.excludeMutingThread(q, muterProp, muteeProp, 'orWhere'); + } + + /** + * Adds AND condition that muterProp (user ID) is not muting muteeProp (note ID). * Both props should be expressions, not raw values. */ @bindThis - public excludeMutingThread(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere' = 'andWhere'): Q { + public andNotMutingThread(q: Q, muterProp: string, muteeProp: string): Q { + return this.excludeMutingThread(q, muterProp, muteeProp, 'andWhere'); + } + + private excludeMutingThread(q: Q, muterProp: string, muteeProp: string, join: 'andWhere' | 'orWhere'): Q { const threadMutedQuery = this.noteThreadMutingsRepository.createQueryBuilder('threadMuted') .select('1') .andWhere(`threadMuted.userId = ${muterProp}`) -- cgit v1.2.3-freya From c76a5467f6279726c0a36de8961cb66252171770 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Thu, 5 Jun 2025 00:30:58 -0400 Subject: use index when checking visibleUserIds and mentions in generateVisibilityQuery --- packages/backend/src/core/QueryService.ts | 33 ++++++++++++++++++++++++++++--- 1 file changed, 30 insertions(+), 3 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index bf5b0b359f..d488505afd 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -160,15 +160,15 @@ export class QueryService { // Reply to me .orWhere(':meId = note.replyUserId') // DM to me - .orWhere(':meId = ANY (note.visibleUserIds)') + .orWhere(':meIdAsList <@ note.visibleUserIds') // Mentions me - .orWhere(':meId = ANY (note.mentions)') + .orWhere(':meIdAsList <@ note.mentions') // Followers-only post .orWhere(new Brackets(qb => this .andFollowingUser(qb, ':meId', 'note.userId') .andWhere('note.visibility = \'followers\''))); - q.setParameters({ meId: me.id }); + q.setParameters({ meId: me.id, meIdAsList: [me.id] }); } })); } @@ -283,6 +283,33 @@ export class QueryService { return q[join](`EXISTS (${followingQuery.getQuery()})`, followingQuery.getParameters()); }; + /** + * Adds OR condition that followerProp (user ID) is following followeeProp (channel ID). + * Both props should be expressions, not raw values. + */ + @bindThis + public orFollowingChannel(q: Q, followerProp: string, followeeProp: string): Q { + return this.addFollowingChannel(q, followerProp, followeeProp, 'orWhere'); + } + + /** + * Adds AND condition that followerProp (user ID) is following followeeProp (channel ID). + * Both props should be expressions, not raw values. + */ + @bindThis + public andFollowingChannel(q: Q, followerProp: string, followeeProp: string): Q { + return this.addFollowingChannel(q, followerProp, followeeProp, 'andWhere'); + } + + private addFollowingChannel(q: Q, followerProp: string, followeeProp: string, join: 'andWhere' | 'orWhere'): Q { + const followingQuery = this.channelFollowingsRepository.createQueryBuilder('following') + .select('1') + .andWhere(`following.followerId = ${followerProp}`) + .andWhere(`following.followeeId = ${followeeProp}`); + + return q[join](`EXISTS (${followingQuery.getQuery()})`, followingQuery.getParameters()); + } + /** * Adds OR condition that blockerProp (user ID) is not blocking blockeeProp (user ID). * Both props should be expressions, not raw values. -- cgit v1.2.3-freya From 05d7aa0b91525e9029b1e8a638561bf125ca32cb Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Thu, 5 Jun 2025 02:34:57 -0400 Subject: additional fixes and cleanup to all note endpoints --- packages/backend/src/core/LatestNoteService.ts | 25 ++--- packages/backend/src/core/QueryService.ts | 125 +++++++++++++++++++-- .../src/server/api/endpoints/antennas/notes.ts | 4 +- .../src/server/api/endpoints/channels/timeline.ts | 12 +- .../api/endpoints/drive/files/attached-notes.ts | 20 +++- packages/backend/src/server/api/endpoints/notes.ts | 23 +++- .../server/api/endpoints/notes/bubble-timeline.ts | 7 +- .../src/server/api/endpoints/notes/children.ts | 28 ++--- .../src/server/api/endpoints/notes/following.ts | 10 +- .../server/api/endpoints/notes/global-timeline.ts | 4 +- .../server/api/endpoints/notes/hybrid-timeline.ts | 61 ++++------ .../server/api/endpoints/notes/local-timeline.ts | 27 ++--- .../src/server/api/endpoints/notes/renotes.ts | 14 ++- .../src/server/api/endpoints/notes/replies.ts | 11 +- .../server/api/endpoints/notes/search-by-tag.ts | 30 ++--- .../src/server/api/endpoints/notes/timeline.ts | 11 +- .../api/endpoints/notes/user-list-timeline.ts | 43 +++---- .../src/server/api/endpoints/roles/notes.ts | 5 +- .../src/server/api/endpoints/users/notes.ts | 26 +---- 19 files changed, 283 insertions(+), 203 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/LatestNoteService.ts b/packages/backend/src/core/LatestNoteService.ts index c379805506..63f973c6c6 100644 --- a/packages/backend/src/core/LatestNoteService.ts +++ b/packages/backend/src/core/LatestNoteService.ts @@ -7,6 +7,7 @@ import { DI } from '@/di-symbols.js'; import type { LatestNotesRepository, NotesRepository } from '@/models/_.js'; import { LoggerService } from '@/core/LoggerService.js'; import Logger from '@/logger.js'; +import { QueryService } from './QueryService.js'; @Injectable() export class LatestNoteService { @@ -14,11 +15,12 @@ export class LatestNoteService { constructor( @Inject(DI.notesRepository) - private notesRepository: NotesRepository, + private readonly notesRepository: NotesRepository, @Inject(DI.latestNotesRepository) - private latestNotesRepository: LatestNotesRepository, + private readonly latestNotesRepository: LatestNotesRepository, + private readonly queryService: QueryService, loggerService: LoggerService, ) { this.logger = loggerService.getLogger('LatestNoteService'); @@ -91,7 +93,7 @@ export class LatestNoteService { // Find the newest remaining note for the user. // We exclude DMs and pure renotes. - const nextLatest = await this.notesRepository + const query = this.notesRepository .createQueryBuilder('note') .select() .where({ @@ -106,18 +108,11 @@ export class LatestNoteService { ? Not(null) : null, }) - .andWhere(` - ( - note."renoteId" IS NULL - OR note.text IS NOT NULL - OR note.cw IS NOT NULL - OR note."replyId" IS NOT NULL - OR note."hasPoll" - OR note."fileIds" != '{}' - ) - `) - .orderBy({ id: 'DESC' }) - .getOne(); + .orderBy({ id: 'DESC' }); + + this.queryService.andIsNotRenote(query, 'note'); + + const nextLatest = await query.getOne(); if (!nextLatest) return; // Record it as the latest diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index d488505afd..4089fc080c 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -188,15 +188,8 @@ export class QueryService { } @bindThis - public generateExcludedRenotesQueryForNotes(q: SelectQueryBuilder): SelectQueryBuilder { - return q - .andWhere(new Brackets(qb => qb - .orWhere('note.renoteId IS NULL') - .orWhere('note.text IS NOT NULL') - .orWhere('note.cw IS NOT NULL') - .orWhere('note.replyId IS NOT NULL') - .orWhere('note.hasPoll = true') - .orWhere('note.fileIds != \'{}\''))); + public generateExcludedRenotesQueryForNotes(q: Q): Q { + return this.andIsNotRenote(q, 'note'); } @bindThis @@ -256,6 +249,120 @@ export class QueryService { return q; } + /** + * Adds OR condition that noteProp (note ID) refers to a quote. + * The prop should be an expression, not a raw value. + */ + @bindThis + public orIsQuote(q: Q, noteProp: string): Q { + return this.addIsQuote(q, noteProp, 'orWhere'); + } + + /** + * Adds AND condition that noteProp (note ID) refers to a quote. + * The prop should be an expression, not a raw value. + */ + @bindThis + public andIsQuote(q: Q, noteProp: string): Q { + return this.addIsQuote(q, noteProp, 'andWhere'); + } + + private addIsQuote(q: Q, noteProp: string, join: 'andWhere' | 'orWhere'): Q { + return q[join](new Brackets(qb => qb + .andWhere(`${noteProp}.renoteId IS NOT NULL`) + .andWhere(new Brackets(qbb => qbb + .orWhere(`${noteProp}.text IS NOT NULL`) + .orWhere(`${noteProp}.cw IS NOT NULL`) + .orWhere(`${noteProp}.replyId IS NOT NULL`) + .orWhere(`${noteProp}.hasPoll = true`) + .orWhere(`${noteProp}.fileIds != '{}'`))))); + } + + /** + * Adds OR condition that noteProp (note ID) does not refer to a quote. + * The prop should be an expression, not a raw value. + */ + @bindThis + public orIsNotQuote(q: Q, noteProp: string): Q { + return this.addIsNotQuote(q, noteProp, 'orWhere'); + } + + /** + * Adds AND condition that noteProp (note ID) does not refer to a quote. + * The prop should be an expression, not a raw value. + */ + @bindThis + public andIsNotQuote(q: Q, noteProp: string): Q { + return this.addIsNotQuote(q, noteProp, 'andWhere'); + } + + private addIsNotQuote(q: Q, noteProp: string, join: 'andWhere' | 'orWhere'): Q { + return q[join](new Brackets(qb => qb + .orWhere(`${noteProp}.renoteId IS NULL`) + .orWhere(new Brackets(qb => qb + .andWhere(`${noteProp}.text IS NULL`) + .andWhere(`${noteProp}.cw IS NULL`) + .andWhere(`${noteProp}.replyId IS NULL`) + .andWhere(`${noteProp}.hasPoll = false`) + .andWhere(`${noteProp}.fileIds = '{}'`))))); + } + + /** + * Adds OR condition that noteProp (note ID) refers to a renote. + * The prop should be an expression, not a raw value. + */ + @bindThis + public orIsRenote(q: Q, noteProp: string): Q { + return this.addIsRenote(q, noteProp, 'orWhere'); + } + + /** + * Adds AND condition that noteProp (note ID) refers to a renote. + * The prop should be an expression, not a raw value. + */ + @bindThis + public andIsRenote(q: Q, noteProp: string): Q { + return this.addIsRenote(q, noteProp, 'andWhere'); + } + + private addIsRenote(q: Q, noteProp: string, join: 'andWhere' | 'orWhere'): Q { + return q[join](new Brackets(qb => qb + .andWhere(`${noteProp}.renoteId IS NOT NULL`) + .andWhere(`${noteProp}.text IS NULL`) + .andWhere(`${noteProp}.cw IS NULL`) + .andWhere(`${noteProp}.replyId IS NULL`) + .andWhere(`${noteProp}.hasPoll = false`) + .andWhere(`${noteProp}.fileIds = '{}'`))); + } + + /** + * Adds OR condition that noteProp (note ID) does not refer to a renote. + * The prop should be an expression, not a raw value. + */ + @bindThis + public orIsNotRenote(q: Q, noteProp: string): Q { + return this.addIsNotRenote(q, noteProp, 'orWhere'); + } + + /** + * Adds AND condition that noteProp (note ID) does not refer to a renote. + * The prop should be an expression, not a raw value. + */ + @bindThis + public andIsNotRenote(q: Q, noteProp: string): Q { + return this.addIsNotRenote(q, noteProp, 'andWhere'); + } + + private addIsNotRenote(q: Q, noteProp: string, join: 'andWhere' | 'orWhere'): Q { + return q[join](new Brackets(qb => qb + .orWhere(`${noteProp}.renoteId IS NULL`) + .orWhere(`${noteProp}.text IS NOT NULL`) + .orWhere(`${noteProp}.cw IS NOT NULL`) + .orWhere(`${noteProp}.replyId IS NOT NULL`) + .orWhere(`${noteProp}.hasPoll = true`) + .orWhere(`${noteProp}.fileIds != '{}'`))); + } + /** * Adds OR condition that followerProp (user ID) is following followeeProp (user ID). * Both props should be expressions, not raw values. diff --git a/packages/backend/src/server/api/endpoints/antennas/notes.ts b/packages/backend/src/server/api/endpoints/antennas/notes.ts index 1abeee53d2..e975b9ad0f 100644 --- a/packages/backend/src/server/api/endpoints/antennas/notes.ts +++ b/packages/backend/src/server/api/endpoints/antennas/notes.ts @@ -114,8 +114,8 @@ export default class extends Endpoint { // eslint- .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser', 'replyUser.id = note.replyUserId') - .leftJoinAndSelect('renote.user', 'renoteUser', 'renoteUser.id = note.renoteUserId'); + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); // NOTE: センシティブ除外の設定はこのエンドポイントでは無視する。 // https://github.com/misskey-dev/misskey/pull/15346#discussion_r1929950255 diff --git a/packages/backend/src/server/api/endpoints/channels/timeline.ts b/packages/backend/src/server/api/endpoints/channels/timeline.ts index b7152130d5..fa5b948eca 100644 --- a/packages/backend/src/server/api/endpoints/channels/timeline.ts +++ b/packages/backend/src/server/api/endpoints/channels/timeline.ts @@ -137,12 +137,14 @@ export default class extends Endpoint { // eslint- .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser', 'replyUser.id = note.replyUserId') - .leftJoinAndSelect('renote.user', 'renoteUser', 'renoteUser.id = note.renoteUserId') - .leftJoinAndSelect('note.channel', 'channel'); + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') + .leftJoinAndSelect('note.channel', 'channel') + .limit(ps.limit); - this.queryService.generateBlockedHostQueryForNote(query); this.queryService.generateVisibilityQuery(query, me); + this.queryService.generateBlockedHostQueryForNote(query); + this.queryService.generateSilencedUserQueryForNotes(query, me); if (me) { this.queryService.generateMutedUserQueryForNotes(query, me); this.queryService.generateBlockedUserQueryForNotes(query, me); @@ -159,6 +161,6 @@ export default class extends Endpoint { // eslint- } //#endregion - return await query.limit(ps.limit).getMany(); + return await query.getMany(); } } diff --git a/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts b/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts index 32c2620915..9d70044db8 100644 --- a/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts +++ b/packages/backend/src/server/api/endpoints/drive/files/attached-notes.ts @@ -81,10 +81,22 @@ export default class extends Endpoint { // eslint- throw new ApiError(meta.errors.noSuchFile); } - const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId); - query.andWhere(':file <@ note.fileIds', { file: [file.id] }); - - const notes = await query.limit(ps.limit).getMany(); + const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId) + .andWhere(':file <@ note.fileIds', { file: [file.id] }) + .innerJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') + .limit(ps.limit); + + this.queryService.generateVisibilityQuery(query, me); + this.queryService.generateBlockedHostQueryForNote(query); + this.queryService.generateSilencedUserQueryForNotes(query, me); + this.queryService.generateMutedUserQueryForNotes(query, me); + this.queryService.generateBlockedUserQueryForNotes(query, me); + + const notes = await query.getMany(); return await this.noteEntityService.packMany(notes, me, { detail: true, diff --git a/packages/backend/src/server/api/endpoints/notes.ts b/packages/backend/src/server/api/endpoints/notes.ts index f6c37023e1..00a88521fd 100644 --- a/packages/backend/src/server/api/endpoints/notes.ts +++ b/packages/backend/src/server/api/endpoints/notes.ts @@ -64,7 +64,16 @@ export default class extends Endpoint { // eslint- .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') .leftJoinAndSelect('reply.user', 'replyUser') - .leftJoinAndSelect('renote.user', 'renoteUser'); + .leftJoinAndSelect('renote.user', 'renoteUser') + .limit(ps.limit); + + this.queryService.generateVisibilityQuery(query, me); + this.queryService.generateBlockedHostQueryForNote(query); + if (me) { + this.queryService.generateSilencedUserQueryForNotes(query, me); + this.queryService.generateMutedUserQueryForNotes(query, me); + this.queryService.generateBlockedUserQueryForNotes(query, me); + } if (ps.local) { query.andWhere('note.userHost IS NULL'); @@ -75,7 +84,15 @@ export default class extends Endpoint { // eslint- } if (ps.renote !== undefined) { - query.andWhere(ps.renote ? 'note.renoteId IS NOT NULL' : 'note.renoteId IS NULL'); + if (ps.renote) { + this.queryService.andIsRenote(query, 'note'); + + if (me) { + this.queryService.generateMutedUserRenotesQueryForNotes(query, me); + } + } else { + this.queryService.andIsNotRenote(query, 'note'); + } } if (ps.withFiles !== undefined) { @@ -91,7 +108,7 @@ export default class extends Endpoint { // eslint- // query.isBot = bot; //} - const notes = await query.limit(ps.limit).getMany(); + const notes = await query.getMany(); return await this.noteEntityService.packMany(notes); }); diff --git a/packages/backend/src/server/api/endpoints/notes/bubble-timeline.ts b/packages/backend/src/server/api/endpoints/notes/bubble-timeline.ts index 5f16351b20..84d6aa0dc7 100644 --- a/packages/backend/src/server/api/endpoints/notes/bubble-timeline.ts +++ b/packages/backend/src/server/api/endpoints/notes/bubble-timeline.ts @@ -82,8 +82,9 @@ export default class extends Endpoint { // eslint- .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser', 'replyUser.id = note.replyUserId') - .leftJoinAndSelect('renote.user', 'renoteUser', 'renoteUser.id = note.renoteUserId'); + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') + .limit(ps.limit); // This subquery mess teaches postgres how to use the right indexes. // Using WHERE or ON conditions causes a fallback to full sequence scan, which times out. @@ -114,7 +115,7 @@ export default class extends Endpoint { // eslint- } //#endregion - const timeline = await query.limit(ps.limit).getMany(); + const timeline = await query.getMany(); if (me) { process.nextTick(() => { diff --git a/packages/backend/src/server/api/endpoints/notes/children.ts b/packages/backend/src/server/api/endpoints/notes/children.ts index 8f19d534d4..cf8b11ccb5 100644 --- a/packages/backend/src/server/api/endpoints/notes/children.ts +++ b/packages/backend/src/server/api/endpoints/notes/children.ts @@ -57,26 +57,22 @@ export default class extends Endpoint { // eslint- super(meta, paramDef, async (ps, me) => { const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId) .andWhere(new Brackets(qb => { - qb - .where('note.replyId = :noteId', { noteId: ps.noteId }); - if (ps.showQuotes) { - qb.orWhere(new Brackets(qb => { - qb - .where('note.renoteId = :noteId', { noteId: ps.noteId }) - .andWhere(new Brackets(qb => { - qb - .where('note.text IS NOT NULL') - .orWhere('note.fileIds != \'{}\'') - .orWhere('note.hasPoll = TRUE'); - })); - })); - } + qb.orWhere('note.replyId = :noteId'); + + if (ps.showQuotes) { + qb.orWhere(new Brackets(qbb => this.queryService + .andIsQuote(qbb, 'note') + .andWhere('note.renoteId = :noteId'), + )); + } })) .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') .leftJoinAndSelect('reply.user', 'replyUser') - .leftJoinAndSelect('renote.user', 'renoteUser'); + .leftJoinAndSelect('renote.user', 'renoteUser') + .setParameters({ noteId: ps.noteId }) + .limit(ps.limit); this.queryService.generateVisibilityQuery(query, me); this.queryService.generateBlockedHostQueryForNote(query); @@ -85,7 +81,7 @@ export default class extends Endpoint { // eslint- this.queryService.generateBlockedUserQueryForNotes(query, me); } - const notes = await query.limit(ps.limit).getMany(); + const notes = await query.getMany(); return await this.noteEntityService.packMany(notes, me); }); diff --git a/packages/backend/src/server/api/endpoints/notes/following.ts b/packages/backend/src/server/api/endpoints/notes/following.ts index ac26dbbbc8..0f8c61ab3e 100644 --- a/packages/backend/src/server/api/endpoints/notes/following.ts +++ b/packages/backend/src/server/api/endpoints/notes/following.ts @@ -87,7 +87,7 @@ export default class extends Endpoint { // eslint- const query = this.notesRepository .createQueryBuilder('note') - .setParameter('me', me.id) + .setParameters({ meId: me.id }) // Limit to latest notes .innerJoin( @@ -130,8 +130,8 @@ export default class extends Endpoint { // eslint- .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser', 'replyUser.id = note.replyUserId') - .leftJoinAndSelect('renote.user', 'renoteUser', 'renoteUser.id = note.renoteUserId') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') // Exclude channel notes .andWhere({ channelId: IsNull() }) @@ -177,14 +177,14 @@ export default class extends Endpoint { // eslint- * Limit to followers (they follow us) */ function addFollower>(query: T): T { - return query.innerJoin(MiFollowing, 'follower', 'follower."followerId" = latest.user_id AND follower."followeeId" = :me'); + return query.innerJoin(MiFollowing, 'follower', 'follower."followerId" = latest.user_id AND follower."followeeId" = :meId'); } /** * Limit to followees (we follow them) */ function addFollowee>(query: T): T { - return query.innerJoin(MiFollowing, 'followee', 'followee."followerId" = :me AND followee."followeeId" = latest.user_id'); + return query.innerJoin(MiFollowing, 'followee', 'followee."followerId" = :meId AND followee."followeeId" = latest.user_id'); } /** diff --git a/packages/backend/src/server/api/endpoints/notes/global-timeline.ts b/packages/backend/src/server/api/endpoints/notes/global-timeline.ts index 6ebb3c1676..506ea6fcda 100644 --- a/packages/backend/src/server/api/endpoints/notes/global-timeline.ts +++ b/packages/backend/src/server/api/endpoints/notes/global-timeline.ts @@ -82,8 +82,8 @@ export default class extends Endpoint { // eslint- .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser', 'replyUser.id = note.replyUserId') - .leftJoinAndSelect('renote.user', 'renoteUser', 'renoteUser.id = note.renoteUserId'); + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); this.queryService.generateBlockedHostQueryForNote(query); this.queryService.generateSilencedUserQueryForNotes(query, me); diff --git a/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts b/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts index 083da9090f..a7b104e198 100644 --- a/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts +++ b/packages/backend/src/server/api/endpoints/notes/hybrid-timeline.ts @@ -197,51 +197,32 @@ export default class extends Endpoint { // eslint- withBots: boolean, withRenotes: boolean, }, me: MiLocalUser) { - const followees = await this.userFollowingService.getFollowees(me.id); - const followingChannels = await this.channelFollowingsRepository.find({ - where: { - followerId: me.id, - }, - }); - const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId) - .andWhere(new Brackets(qb => { - if (followees.length > 0) { - const meOrFolloweeIds = [me.id, ...followees.map(f => f.followeeId)]; - qb.where('note.userId IN (:...meOrFolloweeIds)', { meOrFolloweeIds: meOrFolloweeIds }); - qb.orWhere('(note.visibility = \'public\') AND (note.userHost IS NULL)'); - } else { - qb.where('note.userId = :meId', { meId: me.id }); - qb.orWhere('(note.visibility = \'public\') AND (note.userHost IS NULL)'); - } - })) + // 1. by a user I follow, 2. a public local post, 3. my own post + .andWhere(new Brackets(qb => this.queryService + .orFollowingUser(qb, ':meId', 'note.userId') + .orWhere(new Brackets(qbb => qbb + .andWhere('note.visibility = \'public\'') + .andWhere('note.userHost IS NULL'))) + .orWhere(':meId = note.userId'))) + // 1. in a channel I follow, 2. not in a channel + .andWhere(new Brackets(qb => this.queryService + .orFollowingChannel(qb, ':meId', 'note.channelId') + .orWhere('note.channelId IS NULL'))) + .setParameters({ meId: me.id }) .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser', 'replyUser.id = note.replyUserId') - .leftJoinAndSelect('renote.user', 'renoteUser', 'renoteUser.id = note.renoteUserId'); - - if (followingChannels.length > 0) { - const followingChannelIds = followingChannels.map(x => x.followeeId); - - query.andWhere(new Brackets(qb => { - qb.where('note.channelId IN (:...followingChannelIds)', { followingChannelIds }); - qb.orWhere('note.channelId IS NULL'); - })); - } else { - query.andWhere('note.channelId IS NULL'); - } + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') + .limit(ps.limit); if (!ps.withReplies) { - query.andWhere(new Brackets(qb => { - qb - .where('note.replyId IS NULL') // 返信ではない - .orWhere(new Brackets(qb => { - qb // 返信だけど投稿者自身への返信 - .where('note.replyId IS NOT NULL') - .andWhere('note.replyUserId = note.userId'); - })); - })); + query + // 1. Not a reply, 2. a self-reply + .andWhere(new Brackets(qb => qb + .orWhere('note.replyId IS NULL') // 返信ではない + .orWhere('note.replyUserId = note.userId'))); } this.queryService.generateVisibilityQuery(query, me); @@ -263,6 +244,6 @@ export default class extends Endpoint { // eslint- } //#endregion - return await query.limit(ps.limit).getMany(); + return await query.getMany(); } } diff --git a/packages/backend/src/server/api/endpoints/notes/local-timeline.ts b/packages/backend/src/server/api/endpoints/notes/local-timeline.ts index 360528eaed..41b1ee1086 100644 --- a/packages/backend/src/server/api/endpoints/notes/local-timeline.ts +++ b/packages/backend/src/server/api/endpoints/notes/local-timeline.ts @@ -168,8 +168,17 @@ export default class extends Endpoint { // eslint- .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser', 'replyUser.id = note.replyUserId') - .leftJoinAndSelect('renote.user', 'renoteUser', 'renoteUser.id = note.renoteUserId'); + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') + .limit(ps.limit); + + if (!ps.withReplies) { + query + // 1. Not a reply, 2. a self-reply + .andWhere(new Brackets(qb => qb + .orWhere('note.replyId IS NULL') // 返信ではない + .orWhere('note.replyUserId = note.userId'))); + } this.queryService.generateBlockedHostQueryForNote(query); this.queryService.generateSilencedUserQueryForNotes(query, me); @@ -190,18 +199,6 @@ export default class extends Endpoint { // eslint- this.queryService.generateMutedUserRenotesQueryForNotes(query, me); } - if (!ps.withReplies) { - query.andWhere(new Brackets(qb => { - qb - .where('note.replyId IS NULL') // 返信ではない - .orWhere(new Brackets(qb => { - qb // 返信だけど投稿者自身への返信 - .where('note.replyId IS NOT NULL') - .andWhere('note.replyUserId = note.userId'); - })); - })); - } - - return await query.limit(ps.limit).getMany(); + return await query.getMany(); } } diff --git a/packages/backend/src/server/api/endpoints/notes/renotes.ts b/packages/backend/src/server/api/endpoints/notes/renotes.ts index 0f08cc9cf2..be7cb0320f 100644 --- a/packages/backend/src/server/api/endpoints/notes/renotes.ts +++ b/packages/backend/src/server/api/endpoints/notes/renotes.ts @@ -47,7 +47,7 @@ export const paramDef = { type: 'object', properties: { noteId: { type: 'string', format: 'misskey:id' }, - userId: { type: "string", format: "misskey:id" }, + userId: { type: 'string', format: 'misskey:id' }, limit: { type: 'integer', minimum: 1, maximum: 100, default: 10 }, sinceId: { type: 'string', format: 'misskey:id' }, untilId: { type: 'string', format: 'misskey:id' }, @@ -81,19 +81,21 @@ export default class extends Endpoint { // eslint- .leftJoinAndSelect('renote.user', 'renoteUser'); if (ps.userId) { - query.andWhere("user.id = :userId", { userId: ps.userId }); + query.andWhere('user.id = :userId', { userId: ps.userId }); } if (ps.quote) { - query.andWhere("note.text IS NOT NULL"); + this.queryService.andIsQuote(query, 'note'); } else { - query.andWhere("note.text IS NULL"); + this.queryService.andIsRenote(query, 'note'); } this.queryService.generateVisibilityQuery(query, me); this.queryService.generateBlockedHostQueryForNote(query); - if (me) this.queryService.generateMutedUserQueryForNotes(query, me); - if (me) this.queryService.generateBlockedUserQueryForNotes(query, me); + if (me) { + this.queryService.generateMutedUserQueryForNotes(query, me); + this.queryService.generateBlockedUserQueryForNotes(query, me); + } const renotes = await query.limit(ps.limit).getMany(); diff --git a/packages/backend/src/server/api/endpoints/notes/replies.ts b/packages/backend/src/server/api/endpoints/notes/replies.ts index 0882e19182..f79bfaa7df 100644 --- a/packages/backend/src/server/api/endpoints/notes/replies.ts +++ b/packages/backend/src/server/api/endpoints/notes/replies.ts @@ -59,14 +59,17 @@ export default class extends Endpoint { // eslint- .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') .leftJoinAndSelect('reply.user', 'replyUser') - .leftJoinAndSelect('renote.user', 'renoteUser'); + .leftJoinAndSelect('renote.user', 'renoteUser') + .limit(ps.limit); this.queryService.generateVisibilityQuery(query, me); this.queryService.generateBlockedHostQueryForNote(query); - if (me) this.queryService.generateMutedUserQueryForNotes(query, me); - if (me) this.queryService.generateBlockedUserQueryForNotes(query, me); + if (me) { + this.queryService.generateMutedUserQueryForNotes(query, me); + this.queryService.generateBlockedUserQueryForNotes(query, me); + } - const timeline = await query.limit(ps.limit).getMany(); + const timeline = await query.getMany(); return await this.noteEntityService.packMany(timeline, me); }); diff --git a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts index 01bedd9b1d..5064144d9c 100644 --- a/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts +++ b/packages/backend/src/server/api/endpoints/notes/search-by-tag.ts @@ -12,8 +12,6 @@ import { Endpoint } from '@/server/api/endpoint-base.js'; import { QueryService } from '@/core/QueryService.js'; import { NoteEntityService } from '@/core/entities/NoteEntityService.js'; import { DI } from '@/di-symbols.js'; -import { CacheService } from '@/core/CacheService.js'; -import { UtilityService } from '@/core/UtilityService.js'; export const meta = { tags: ['notes', 'hashtags'], @@ -82,19 +80,18 @@ export default class extends Endpoint { // eslint- private noteEntityService: NoteEntityService, private queryService: QueryService, - private cacheService: CacheService, - private utilityService: UtilityService, ) { super(meta, paramDef, async (ps, me) => { const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId) - .andWhere("note.visibility IN ('public', 'home')") // keep in sync with NoteCreateService call to `hashtagService.updateHashtags()` + .andWhere(new Brackets(qb => qb + .orWhere('note.visibility = \'public\'') + .orWhere('note.visibility = \'home\''))) // keep in sync with NoteCreateService call to `hashtagService.updateHashtags()` .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') .leftJoinAndSelect('reply.user', 'replyUser') - .leftJoinAndSelect('renote.user', 'renoteUser'); - - if (!this.serverSettings.enableBotTrending) query.andWhere('user.isBot = FALSE'); + .leftJoinAndSelect('renote.user', 'renoteUser') + .limit(ps.limit); this.queryService.generateBlockedHostQueryForNote(query); this.queryService.generateSilencedUserQueryForNotes(query, me); @@ -102,7 +99,7 @@ export default class extends Endpoint { // eslint- if (me) this.queryService.generateBlockedUserQueryForNotes(query, me); if (me) this.queryService.generateMutedUserRenotesQueryForNotes(query, me); - const followings = me ? await this.cacheService.userFollowingsCache.fetch(me.id) : {}; + if (!this.serverSettings.enableBotTrending) query.andWhere('user.isBot = FALSE'); try { if (ps.tag) { @@ -135,9 +132,9 @@ export default class extends Endpoint { // eslint- if (ps.renote != null) { if (ps.renote) { - query.andWhere('note.renoteId IS NOT NULL'); + this.queryService.andIsRenote(query, 'note'); } else { - query.andWhere('note.renoteId IS NULL'); + this.queryService.andIsNotRenote(query, 'note'); } } @@ -154,16 +151,7 @@ export default class extends Endpoint { // eslint- } // Search notes - let notes = await query.limit(ps.limit).getMany(); - - notes = notes.filter(note => { - if (note.user?.isSilenced && me && followings && note.userId !== me.id && !followings[note.userId]) return false; - if (note.user?.isSuspended) return false; - if (note.userHost) { - if (!this.utilityService.isFederationAllowedHost(note.userHost)) return false; - } - return true; - }); + const notes = await query.getMany(); return await this.noteEntityService.packMany(notes, me); }); diff --git a/packages/backend/src/server/api/endpoints/notes/timeline.ts b/packages/backend/src/server/api/endpoints/notes/timeline.ts index d898748ac7..8cf7bb5795 100644 --- a/packages/backend/src/server/api/endpoints/notes/timeline.ts +++ b/packages/backend/src/server/api/endpoints/notes/timeline.ts @@ -142,11 +142,6 @@ export default class extends Endpoint { // eslint- private async getFromDb(ps: { untilId: string | null; sinceId: string | null; limit: number; withFiles: boolean; withRenotes: boolean; withBots: boolean; }, me: MiLocalUser) { //#region Construct query const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId) - .innerJoinAndSelect('note.user', 'user') - .leftJoinAndSelect('note.reply', 'reply') - .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser') - .leftJoinAndSelect('renote.user', 'renoteUser') // 1. in a channel I follow, 2. my own post, 3. by a user I follow .andWhere(new Brackets(qb => this.queryService .orFollowingChannel(qb, ':meId', 'note.channelId') @@ -160,10 +155,16 @@ export default class extends Endpoint { // eslint- .orWhere('note.replyId IS NULL') // 返信ではない .orWhere('note.replyUserId = note.userId'))) .setParameters({ meId: me.id }) + .innerJoinAndSelect('note.user', 'user') + .leftJoinAndSelect('note.reply', 'reply') + .leftJoinAndSelect('note.renote', 'renote') + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') .limit(ps.limit); this.queryService.generateVisibilityQuery(query, me); this.queryService.generateBlockedHostQueryForNote(query); + this.queryService.generateSilencedUserQueryForNotes(query, me); this.queryService.generateMutedUserQueryForNotes(query, me); this.queryService.generateBlockedUserQueryForNotes(query, me); diff --git a/packages/backend/src/server/api/endpoints/notes/user-list-timeline.ts b/packages/backend/src/server/api/endpoints/notes/user-list-timeline.ts index 8872672b67..0f038e5541 100644 --- a/packages/backend/src/server/api/endpoints/notes/user-list-timeline.ts +++ b/packages/backend/src/server/api/endpoints/notes/user-list-timeline.ts @@ -154,32 +154,25 @@ export default class extends Endpoint { // eslint- //#region Construct query const query = this.queryService.makePaginationQuery(this.notesRepository.createQueryBuilder('note'), ps.sinceId, ps.untilId) .innerJoin(this.userListMembershipsRepository.metadata.targetName, 'userListMemberships', 'userListMemberships.userId = note.userId') + .andWhere('userListMemberships.userListId = :userListId', { userListId: list.id }) + .andWhere('note.channelId IS NULL') // チャンネルノートではない + .andWhere(new Brackets(qb => qb + // 返信ではない + .orWhere('note.replyId IS NULL') + // 返信だけど投稿者自身への返信 + .orWhere('note.replyUserId = note.userId') + // 返信だけど自分宛ての返信 + .orWhere('note.replyUserId = :meId') + // 返信だけどwithRepliesがtrueの場合 + .orWhere('userListMemberships.withReplies = true'), + )) + .setParameters({ meId: me.id }) .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser', 'replyUser.id = note.replyUserId') - .leftJoinAndSelect('renote.user', 'renoteUser', 'renoteUser.id = note.renoteUserId') - .andWhere('userListMemberships.userListId = :userListId', { userListId: list.id }) - .andWhere('note.channelId IS NULL') // チャンネルノートではない - .andWhere(new Brackets(qb => { - qb - .where('note.replyId IS NULL') // 返信ではない - .orWhere(new Brackets(qb => { - qb // 返信だけど投稿者自身への返信 - .where('note.replyId IS NOT NULL') - .andWhere('note.replyUserId = note.userId'); - })) - .orWhere(new Brackets(qb => { - qb // 返信だけど自分宛ての返信 - .where('note.replyId IS NOT NULL') - .andWhere('note.replyUserId = :meId', { meId: me.id }); - })) - .orWhere(new Brackets(qb => { - qb // 返信だけどwithRepliesがtrueの場合 - .where('note.replyId IS NOT NULL') - .andWhere('userListMemberships.withReplies = true'); - })); - })); + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser') + .limit(ps.limit); this.queryService.generateVisibilityQuery(query, me); this.queryService.generateBlockedHostQueryForNote(query); @@ -192,12 +185,12 @@ export default class extends Endpoint { // eslint- if (!ps.withRenotes) { this.queryService.generateExcludedRenotesQueryForNotes(query); - } else if (me) { + } else { this.queryService.generateMutedUserRenotesQueryForNotes(query, me); } //#endregion - return await query.limit(ps.limit).getMany(); + return await query.getMany(); } } diff --git a/packages/backend/src/server/api/endpoints/roles/notes.ts b/packages/backend/src/server/api/endpoints/roles/notes.ts index 4752561ad5..741bd819ba 100644 --- a/packages/backend/src/server/api/endpoints/roles/notes.ts +++ b/packages/backend/src/server/api/endpoints/roles/notes.ts @@ -107,10 +107,11 @@ export default class extends Endpoint { // eslint- .innerJoinAndSelect('note.user', 'user') .leftJoinAndSelect('note.reply', 'reply') .leftJoinAndSelect('note.renote', 'renote') - .leftJoinAndSelect('reply.user', 'replyUser', 'replyUser.id = note.replyUserId') - .leftJoinAndSelect('renote.user', 'renoteUser', 'renoteUser.id = note.renoteUserId'); + .leftJoinAndSelect('reply.user', 'replyUser') + .leftJoinAndSelect('renote.user', 'renoteUser'); this.queryService.generateBlockedHostQueryForNote(query); + this.queryService.generateSilencedUserQueryForNotes(query, me); this.queryService.generateMutedUserQueryForNotes(query, me); this.queryService.generateBlockedUserQueryForNotes(query, me); this.queryService.generateMutedUserRenotesQueryForNotes(query, me); diff --git a/packages/backend/src/server/api/endpoints/users/notes.ts b/packages/backend/src/server/api/endpoints/users/notes.ts index 965baa859a..66b50e0633 100644 --- a/packages/backend/src/server/api/endpoints/users/notes.ts +++ b/packages/backend/src/server/api/endpoints/users/notes.ts @@ -205,7 +205,8 @@ export default class extends Endpoint { // eslint- .leftJoinAndSelect('note.renote', 'renote') .leftJoinAndSelect('note.channel', 'channel') .leftJoinAndSelect('reply.user', 'replyUser') - .leftJoinAndSelect('renote.user', 'renoteUser'); + .leftJoinAndSelect('renote.user', 'renoteUser') + .limit(ps.limit); if (ps.withChannelNotes) { if (!isSelf) query.andWhere(new Brackets(qb => { @@ -230,26 +231,9 @@ export default class extends Endpoint { // eslint- if (!ps.withRenotes && !ps.withQuotes) { query.andWhere('note.renoteId IS NULL'); } else if (!ps.withRenotes) { - query.andWhere(new Brackets(qb => { - qb.orWhere('note.userId != :userId', { userId: ps.userId }); - qb.orWhere('note.renoteId IS NULL'); - qb.orWhere('note.text IS NOT NULL'); - qb.orWhere('note.fileIds != \'{}\''); - qb.orWhere('0 < (SELECT COUNT(*) FROM poll WHERE poll."noteId" = note.id)'); - })); + this.queryService.andIsNotRenote(query, 'note'); } else if (!ps.withQuotes) { - query.andWhere(` - ( - note."renoteId" IS NULL - OR ( - note.text IS NULL - AND note.cw IS NULL - AND note."replyId" IS NULL - AND note."hasPoll" IS FALSE - AND note."fileIds" = '{}' - ) - ) - `); + this.queryService.andIsNotQuote(query, 'note'); } if (!ps.withRepliesToOthers && !ps.withRepliesToSelf) { @@ -268,6 +252,6 @@ export default class extends Endpoint { // eslint- query.andWhere('"user"."isBot" = false'); } - return await query.limit(ps.limit).getMany(); + return await query.getMany(); } } -- cgit v1.2.3-freya From 5e7d0e9acc0762261c71734a61cd0788a63ae246 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Fri, 6 Jun 2025 02:32:32 -0400 Subject: fix typo in QueryService.generateBlockQueryForUsers --- packages/backend/src/core/QueryService.ts | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index 4089fc080c..f9e4804e14 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -94,7 +94,7 @@ export class QueryService { @bindThis public generateBlockQueryForUsers(q: SelectQueryBuilder, me: { id: MiUser['id'] }): SelectQueryBuilder { this.andNotBlockingUser(q, ':meId', 'user.id'); - this.andNotBlockingUser(q, 'user.id', ':me.id'); + this.andNotBlockingUser(q, 'user.id', ':meId'); return q.setParameters({ meId: me.id }); } -- cgit v1.2.3-freya From 27c27529f6b06374fed505ba87c6ee1165d23983 Mon Sep 17 00:00:00 2001 From: Hazelnoot Date: Tue, 10 Jun 2025 19:52:47 -0400 Subject: enforce DM visibility in generateVisibilityQuery --- packages/backend/src/core/QueryService.ts | 16 +++++++++------- 1 file changed, 9 insertions(+), 7 deletions(-) (limited to 'packages/backend/src/core/QueryService.ts') diff --git a/packages/backend/src/core/QueryService.ts b/packages/backend/src/core/QueryService.ts index 4089fc080c..2d8ea51e65 100644 --- a/packages/backend/src/core/QueryService.ts +++ b/packages/backend/src/core/QueryService.ts @@ -157,15 +157,17 @@ export class QueryService { qb // My post .orWhere(':meId = note.userId') - // Reply to me - .orWhere(':meId = note.replyUserId') - // DM to me + // Visible to me .orWhere(':meIdAsList <@ note.visibleUserIds') - // Mentions me - .orWhere(':meIdAsList <@ note.mentions') // Followers-only post - .orWhere(new Brackets(qb => this - .andFollowingUser(qb, ':meId', 'note.userId') + .orWhere(new Brackets(qb => qb + .andWhere(new Brackets(qbb => this + // Following author + .orFollowingUser(qbb, ':meId', 'note.userId') + // Mentions me + .orWhere(':meIdAsList <@ note.mentions') + // Reply to me + .orWhere(':meId = note.replyUserId'))) .andWhere('note.visibility = \'followers\''))); q.setParameters({ meId: me.id, meIdAsList: [me.id] }); -- cgit v1.2.3-freya