From fbd67e7f386504e50f2504cb6386700a58906f16 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Wed, 28 Jul 2021 16:40:21 +0200 Subject: Add ability to search by uuids/actor names --- .../video/sql/videos-id-list-query-builder.ts | 10 +++ server/models/video/video-channel.ts | 76 ++++++++++++++-------- server/models/video/video-playlist.ts | 16 ++++- server/models/video/video.ts | 3 + 4 files changed, 75 insertions(+), 30 deletions(-) (limited to 'server/models/video') diff --git a/server/models/video/sql/videos-id-list-query-builder.ts b/server/models/video/sql/videos-id-list-query-builder.ts index d4260c69c..7625c003d 100644 --- a/server/models/video/sql/videos-id-list-query-builder.ts +++ b/server/models/video/sql/videos-id-list-query-builder.ts @@ -35,6 +35,8 @@ export type BuildVideosListQueryOptions = { tagsOneOf?: string[] tagsAllOf?: string[] + uuids?: string[] + withFiles?: boolean accountId?: number @@ -161,6 +163,10 @@ export class VideosIdListQueryBuilder extends AbstractVideosQueryBuilder { this.whereTagsAllOf(options.tagsAllOf) } + if (options.uuids) { + this.whereUUIDs(options.uuids) + } + if (options.nsfw === true) { this.whereNSFW() } else if (options.nsfw === false) { @@ -386,6 +392,10 @@ export class VideosIdListQueryBuilder extends AbstractVideosQueryBuilder { ) } + private whereUUIDs (uuids: string[]) { + this.and.push('"video"."uuid" IN (' + createSafeIn(this.sequelize, uuids) + ')') + } + private whereCategoryOneOf (categoryOneOf: number[]) { this.and.push('"video"."category" IN (:categoryOneOf)') this.replacements.categoryOneOf = categoryOneOf diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts index 9aa271711..327f49304 100644 --- a/server/models/video/video-channel.ts +++ b/server/models/video/video-channel.ts @@ -59,6 +59,7 @@ type AvailableForListOptions = { actorId: number search?: string host?: string + names?: string[] } type AvailableWithStatsOptions = { @@ -84,18 +85,20 @@ export type SummaryOptions = { // Only list local channels OR channels that are on an instance followed by actorId const inQueryInstanceFollow = buildServerIdsFollowedBy(options.actorId) - const whereActor = { - [Op.or]: [ - { - serverId: null - }, - { - serverId: { - [Op.in]: Sequelize.literal(inQueryInstanceFollow) + const whereActorAnd: WhereOptions[] = [ + { + [Op.or]: [ + { + serverId: null + }, + { + serverId: { + [Op.in]: Sequelize.literal(inQueryInstanceFollow) + } } - } - ] - } + ] + } + ] let serverRequired = false let whereServer: WhereOptions @@ -106,8 +109,16 @@ export type SummaryOptions = { } if (options.host === WEBSERVER.HOST) { - Object.assign(whereActor, { - [Op.and]: [ { serverId: null } ] + whereActorAnd.push({ + serverId: null + }) + } + + if (options.names) { + whereActorAnd.push({ + preferredUsername: { + [Op.in]: options.names + } }) } @@ -118,7 +129,9 @@ export type SummaryOptions = { exclude: unusedActorAttributesForAPI }, model: ActorModel, - where: whereActor, + where: { + [Op.and]: whereActorAnd + }, include: [ { model: ServerModel, @@ -454,26 +467,23 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` static searchForApi (options: { actorId: number - search: string + search?: string start: number count: number sort: string host?: string + names?: string[] }) { - const attributesInclude = [] - const escapedSearch = VideoChannelModel.sequelize.escape(options.search) - const escapedLikeSearch = VideoChannelModel.sequelize.escape('%' + options.search + '%') - attributesInclude.push(createSimilarityAttribute('VideoChannelModel.name', options.search)) + let attributesInclude: any[] = [ literal('0 as similarity') ] + let where: WhereOptions - const query = { - attributes: { - include: attributesInclude - }, - offset: options.start, - limit: options.count, - order: getSort(options.sort), - where: { + if (options.search) { + const escapedSearch = VideoChannelModel.sequelize.escape(options.search) + const escapedLikeSearch = VideoChannelModel.sequelize.escape('%' + options.search + '%') + attributesInclude = [ createSimilarityAttribute('VideoChannelModel.name', options.search) ] + + where = { [Op.or]: [ Sequelize.literal( 'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))' @@ -485,9 +495,19 @@ ON "Account->Actor"."serverId" = "Account->Actor->Server"."id"` } } + const query = { + attributes: { + include: attributesInclude + }, + offset: options.start, + limit: options.count, + order: getSort(options.sort), + where + } + return VideoChannelModel .scope({ - method: [ ScopeNames.FOR_API, { actorId: options.actorId, host: options.host } as AvailableForListOptions ] + method: [ ScopeNames.FOR_API, { actorId: options.actorId, host: options.host, names: options.names } as AvailableForListOptions ] }) .findAndCountAll(query) .then(({ rows, count }) => { diff --git a/server/models/video/video-playlist.ts b/server/models/video/video-playlist.ts index a2dc7075d..caa79952d 100644 --- a/server/models/video/video-playlist.ts +++ b/server/models/video/video-playlist.ts @@ -83,6 +83,7 @@ type AvailableForListOptions = { listMyPlaylists?: boolean search?: string host?: string + uuids?: string[] withVideos?: boolean } @@ -200,18 +201,26 @@ function getVideoLengthSelect () { }) } + if (options.uuids) { + whereAnd.push({ + uuid: { + [Op.in]: options.uuids + } + }) + } + if (options.withVideos === true) { whereAnd.push( literal(`(${getVideoLengthSelect()}) != 0`) ) } - const attributesInclude = [] + let attributesInclude: any[] = [ literal('0 as similarity') ] if (options.search) { const escapedSearch = VideoPlaylistModel.sequelize.escape(options.search) const escapedLikeSearch = VideoPlaylistModel.sequelize.escape('%' + options.search + '%') - attributesInclude.push(createSimilarityAttribute('VideoPlaylistModel.name', options.search)) + attributesInclude = [ createSimilarityAttribute('VideoPlaylistModel.name', options.search) ] whereAnd.push({ [Op.or]: [ @@ -359,6 +368,7 @@ export class VideoPlaylistModel extends Model>> { durationMax?: number // seconds user?: MUserAccountId filter?: VideoFilter + uuids?: string[] }) { const serverActor = await getServerActor() @@ -1167,6 +1168,8 @@ export class VideoModel extends Model>> { durationMin: options.durationMin, durationMax: options.durationMax, + uuids: options.uuids, + search: options.search } -- cgit v1.2.3