From dec69cc67b060eec244eb8d2df7dcd362359514b Mon Sep 17 00:00:00 2001 From: syuilo Date: Sun, 17 Oct 2021 16:26:35 +0900 Subject: enhance: ユーザー検索の精度を強化 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- .../endpoints/users/search-by-username-and-host.ts | 9 +- src/server/api/endpoints/users/search.ts | 127 +++++++++++++-------- 2 files changed, 88 insertions(+), 48 deletions(-) (limited to 'src/server') diff --git a/src/server/api/endpoints/users/search-by-username-and-host.ts b/src/server/api/endpoints/users/search-by-username-and-host.ts index b9fbf48fb2..8fdc710658 100644 --- a/src/server/api/endpoints/users/search-by-username-and-host.ts +++ b/src/server/api/endpoints/users/search-by-username-and-host.ts @@ -1,6 +1,8 @@ import $ from 'cafy'; import define from '../../define'; import { Users } from '@/models/index'; +import { Brackets } from 'typeorm'; +import { USER_ACTIVE_THRESHOLD } from '@/const'; export const meta = { tags: ['users'], @@ -64,8 +66,11 @@ export default define(meta, async (ps, me) => { .where('user.host IS NULL') .andWhere('user.isSuspended = FALSE') .andWhere('user.usernameLower like :username', { username: ps.username.toLowerCase() + '%' }) - .andWhere('user.updatedAt IS NOT NULL') - .orderBy('user.updatedAt', 'DESC') + .andWhere(new Brackets(qb => { qb + .where('user.lastActiveDate IS NULL') + .orWhere('user.lastActiveDate > :activeThreshold', { activeThreshold: new Date(Date.now() - USER_ACTIVE_THRESHOLD) }); + })) + .orderBy('user.lastActiveDate', 'DESC', 'NULLS LAST') .take(ps.limit!) .skip(ps.offset) .getMany(); diff --git a/src/server/api/endpoints/users/search.ts b/src/server/api/endpoints/users/search.ts index 8011d90b3d..e4fbfedd10 100644 --- a/src/server/api/endpoints/users/search.ts +++ b/src/server/api/endpoints/users/search.ts @@ -2,6 +2,8 @@ import $ from 'cafy'; import define from '../../define'; import { UserProfiles, Users } from '@/models/index'; import { User } from '@/models/entities/user'; +import { Brackets } from 'typeorm'; +import { USER_ACTIVE_THRESHOLD } from '@/const'; export const meta = { tags: ['users'], @@ -23,9 +25,9 @@ export const meta = { default: 10, }, - localOnly: { - validator: $.optional.bool, - default: false, + scope: { + validator: $.optional.str.or(['local', 'remote', 'both']), + default: 'both', }, detail: { @@ -51,58 +53,91 @@ export default define(meta, async (ps, me) => { let users: User[] = []; if (isUsername) { - users = await Users.createQueryBuilder('user') - .where('user.host IS NULL') - .andWhere('user.isSuspended = FALSE') - .andWhere('user.usernameLower like :username', { username: ps.query.replace('@', '').toLowerCase() + '%' }) - .andWhere('user.updatedAt IS NOT NULL') - .orderBy('user.updatedAt', 'DESC') + const usernameQuery = Users.createQueryBuilder('user') + .where('user.usernameLower like :username', { username: ps.query.replace('@', '').toLowerCase() + '%' }) + .andWhere(new Brackets(qb => { qb + .where('user.lastActiveDate IS NULL') + .orWhere('user.lastActiveDate > :activeThreshold', { activeThreshold: new Date(Date.now() - USER_ACTIVE_THRESHOLD) }); + })) + .andWhere('user.isSuspended = FALSE'); + + if (ps.scope === 'local') { + usernameQuery + .andWhere('user.host IS NULL') + .orderBy('user.lastActiveDate', 'DESC', 'NULLS LAST'); + } else if (ps.scope === 'remote') { + usernameQuery + .andWhere('user.host IS NOT NULL') + .orderBy('user.updatedAt', 'DESC', 'NULLS LAST'); + } else { // both + usernameQuery + .orderBy('user.updatedAt', 'DESC', 'NULLS LAST'); + } + + users = await usernameQuery .take(ps.limit!) .skip(ps.offset) .getMany(); - - if (users.length < ps.limit! && !ps.localOnly) { - const otherUsers = await Users.createQueryBuilder('user') - .where('user.host IS NOT NULL') - .andWhere('user.isSuspended = FALSE') - .andWhere('user.usernameLower like :username', { username: ps.query.replace('@', '').toLowerCase() + '%' }) - .andWhere('user.updatedAt IS NOT NULL') - .orderBy('user.updatedAt', 'DESC') - .take(ps.limit! - users.length) - .getMany(); - - users = users.concat(otherUsers); - } } else { - const profQuery = UserProfiles.createQueryBuilder('prof') - .select('prof.userId') - .where('prof.userHost IS NULL') - .andWhere('prof.description ilike :query', { query: '%' + ps.query + '%' }); - - users = await Users.createQueryBuilder('user') - .where(`user.id IN (${ profQuery.getQuery() })`) - .setParameters(profQuery.getParameters()) - .andWhere('user.updatedAt IS NOT NULL') - .orderBy('user.updatedAt', 'DESC') + const nameQuery = Users.createQueryBuilder('user') + .where('user.name ilike :query', { query: '%' + ps.query + '%' }) + .andWhere(new Brackets(qb => { qb + .where('user.lastActiveDate IS NULL') + .orWhere('user.lastActiveDate > :activeThreshold', { activeThreshold: new Date(Date.now() - USER_ACTIVE_THRESHOLD) }); + })) + .andWhere('user.isSuspended = FALSE'); + + if (ps.scope === 'local') { + nameQuery + .andWhere('user.host IS NULL') + .orderBy('user.lastActiveDate', 'DESC', 'NULLS LAST'); + } else if (ps.scope === 'remote') { + nameQuery + .andWhere('user.host IS NOT NULL') + .orderBy('user.updatedAt', 'DESC', 'NULLS LAST'); + } else { // both + nameQuery + .orderBy('user.updatedAt', 'DESC', 'NULLS LAST'); + } + + users = await nameQuery .take(ps.limit!) .skip(ps.offset) .getMany(); - if (users.length < ps.limit! && !ps.localOnly) { - const profQuery2 = UserProfiles.createQueryBuilder('prof') + if (users.length < ps.limit!) { + const profQuery = UserProfiles.createQueryBuilder('prof') .select('prof.userId') - .where('prof.userHost IS NOT NULL') - .andWhere('prof.description ilike :query', { query: '%' + ps.query + '%' }); - - const otherUsers = await Users.createQueryBuilder('user') - .where(`user.id IN (${ profQuery2.getQuery() })`) - .setParameters(profQuery2.getParameters()) - .andWhere('user.updatedAt IS NOT NULL') - .orderBy('user.updatedAt', 'DESC') - .take(ps.limit! - users.length) - .getMany(); - - users = users.concat(otherUsers); + .where('prof.description ilike :query', { query: '%' + ps.query + '%' }); + + if (ps.scope === 'local') { + profQuery.andWhere('prof.userHost IS NULL'); + } else if (ps.scope === 'remote') { + profQuery.andWhere('prof.userHost IS NOT NULL'); + } + + const query = Users.createQueryBuilder('user') + .where(`user.id IN (${ profQuery.getQuery() })`) + .andWhere(new Brackets(qb => { qb + .where('user.lastActiveDate IS NULL') + .orWhere('user.lastActiveDate > :activeThreshold', { activeThreshold: new Date(Date.now() - USER_ACTIVE_THRESHOLD) }); + })) + .andWhere('user.isSuspended = FALSE') + .setParameters(profQuery.getParameters()); + + if (ps.scope === 'local') { + query.orderBy('user.lastActiveDate', 'DESC', 'NULLS LAST'); + } else if (ps.scope === 'remote') { + query.orderBy('user.updatedAt', 'DESC', 'NULLS LAST'); + } else { // both + query.orderBy('user.updatedAt', 'DESC', 'NULLS LAST'); + } + + users = users.concat(await query + .take(ps.limit!) + .skip(ps.offset) + .getMany() + ); } } -- cgit v1.2.3-freya