From 57c36b277e68b764dd34cb2e449f6e2ca3d1e9b6 Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Thu, 19 Jul 2018 16:17:54 +0200 Subject: Begin advanced search --- server/models/activitypub/actor.ts | 6 +++ server/models/utils.ts | 52 ++++++++++++++++++++- server/models/video/video.ts | 92 +++++++++++++++----------------------- 3 files changed, 93 insertions(+), 57 deletions(-) (limited to 'server/models') diff --git a/server/models/activitypub/actor.ts b/server/models/activitypub/actor.ts index 1d0e54ee3..38a689fea 100644 --- a/server/models/activitypub/actor.ts +++ b/server/models/activitypub/actor.ts @@ -88,6 +88,12 @@ enum ScopeNames { }, { fields: [ 'inboxUrl', 'sharedInboxUrl' ] + }, + { + fields: [ 'serverId' ] + }, + { + fields: [ 'avatarId' ] } ] }) diff --git a/server/models/utils.ts b/server/models/utils.ts index 59ce83c16..49d32c24f 100644 --- a/server/models/utils.ts +++ b/server/models/utils.ts @@ -1,6 +1,8 @@ // Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ] +import { Sequelize } from 'sequelize-typescript' + function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) { - let field: string + let field: any let direction: 'ASC' | 'DESC' if (value.substring(0, 1) === '-') { @@ -11,6 +13,9 @@ function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) { field = value } + // Alias + if (field.toLowerCase() === 'bestmatch') field = Sequelize.col('similarity') + return [ [ field, direction ], lastSort ] } @@ -27,10 +32,53 @@ function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldN } } +function buildTrigramSearchIndex (indexName: string, attribute: string) { + return { + name: indexName, + fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + '))') as any ], + using: 'gin', + operator: 'gin_trgm_ops' + } +} + +function createSimilarityAttribute (col: string, value: string) { + return Sequelize.fn( + 'similarity', + + searchTrigramNormalizeCol(col), + + searchTrigramNormalizeValue(value) + ) +} + +function createSearchTrigramQuery (col: string, value: string) { + return { + [ Sequelize.Op.or ]: [ + // FIXME: use word_similarity instead of just similarity? + Sequelize.where(searchTrigramNormalizeCol(col), ' % ', searchTrigramNormalizeValue(value)), + + Sequelize.where(searchTrigramNormalizeCol(col), ' LIKE ', searchTrigramNormalizeValue(`%${value}%`)) + ] + } +} + // --------------------------------------------------------------------------- export { getSort, getSortOnModel, - throwIfNotValid + createSimilarityAttribute, + throwIfNotValid, + buildTrigramSearchIndex, + createSearchTrigramQuery +} + +// --------------------------------------------------------------------------- + +function searchTrigramNormalizeValue (value: string) { + return Sequelize.fn('lower', Sequelize.fn('unaccent', value)) +} + +function searchTrigramNormalizeCol (col: string) { + return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col))) } diff --git a/server/models/video/video.ts b/server/models/video/video.ts index 74a3a5d05..15b4dda5b 100644 --- a/server/models/video/video.ts +++ b/server/models/video/video.ts @@ -83,7 +83,7 @@ import { AccountVideoRateModel } from '../account/account-video-rate' import { ActorModel } from '../activitypub/actor' import { AvatarModel } from '../avatar/avatar' import { ServerModel } from '../server/server' -import { getSort, throwIfNotValid } from '../utils' +import { buildTrigramSearchIndex, createSearchTrigramQuery, createSimilarityAttribute, getSort, throwIfNotValid } from '../utils' import { TagModel } from './tag' import { VideoAbuseModel } from './video-abuse' import { VideoChannelModel } from './video-channel' @@ -94,6 +94,37 @@ import { VideoTagModel } from './video-tag' import { ScheduleVideoUpdateModel } from './schedule-video-update' import { VideoCaptionModel } from './video-caption' +// FIXME: Define indexes here because there is an issue with TS and Sequelize.literal when called directly in the annotation +const indexes: Sequelize.DefineIndexesOptions[] = [ + buildTrigramSearchIndex('video_name_trigram', 'name'), + + { + fields: [ 'createdAt' ] + }, + { + fields: [ 'duration' ] + }, + { + fields: [ 'views' ] + }, + { + fields: [ 'likes' ] + }, + { + fields: [ 'uuid' ] + }, + { + fields: [ 'channelId' ] + }, + { + fields: [ 'id', 'privacy', 'state', 'waitTranscoding' ] + }, + { + fields: [ 'url'], + unique: true + } +] + export enum ScopeNames { AVAILABLE_FOR_LIST = 'AVAILABLE_FOR_LIST', WITH_ACCOUNT_DETAILS = 'WITH_ACCOUNT_DETAILS', @@ -309,36 +340,7 @@ export enum ScopeNames { }) @Table({ tableName: 'video', - indexes: [ - { - fields: [ 'name' ] - }, - { - fields: [ 'createdAt' ] - }, - { - fields: [ 'duration' ] - }, - { - fields: [ 'views' ] - }, - { - fields: [ 'likes' ] - }, - { - fields: [ 'uuid' ] - }, - { - fields: [ 'channelId' ] - }, - { - fields: [ 'id', 'privacy', 'state', 'waitTranscoding' ] - }, - { - fields: [ 'url'], - unique: true - } - ] + indexes }) export class VideoModel extends Model { @@ -794,33 +796,13 @@ export class VideoModel extends Model { static async searchAndPopulateAccountAndServer (value: string, start: number, count: number, sort: string, hideNSFW: boolean) { const query: IFindOptions = { + attributes: { + include: [ createSimilarityAttribute('VideoModel.name', value) ] + }, offset: start, limit: count, order: getSort(sort), - where: { - [Sequelize.Op.or]: [ - { - name: { - [ Sequelize.Op.iLike ]: '%' + value + '%' - } - }, - { - preferredUsernameChannel: Sequelize.where(Sequelize.col('VideoChannel->Actor.preferredUsername'), { - [ Sequelize.Op.iLike ]: '%' + value + '%' - }) - }, - { - preferredUsernameAccount: Sequelize.where(Sequelize.col('VideoChannel->Account->Actor.preferredUsername'), { - [ Sequelize.Op.iLike ]: '%' + value + '%' - }) - }, - { - host: Sequelize.where(Sequelize.col('VideoChannel->Account->Actor->Server.host'), { - [ Sequelize.Op.iLike ]: '%' + value + '%' - }) - } - ] - } + where: createSearchTrigramQuery('VideoModel.name', value) } const serverActor = await getServerActor() -- cgit v1.2.3