From c3c2ab1c8b52945df567c59118a7c77fdd6a1d4d Mon Sep 17 00:00:00 2001 From: Chocobozzz Date: Tue, 28 Aug 2018 15:16:04 +0200 Subject: Optimize channel search query --- server/models/video/video-channel.ts | 21 ++++++++++----------- 1 file changed, 10 insertions(+), 11 deletions(-) (limited to 'server/models') diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts index 7d717fc68..e70e52515 100644 --- a/server/models/video/video-channel.ts +++ b/server/models/video/video-channel.ts @@ -69,9 +69,9 @@ type AvailableForListOptions = { // Only list local channels OR channels that are on an instance followed by actorId const inQueryInstanceFollow = '(' + - 'SELECT "actor"."serverId" FROM "actor" ' + - 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = actor.id ' + - 'WHERE "actorFollow"."actorId" = ' + actorIdNumber + + 'SELECT "actor"."serverId" FROM "actorFollow" ' + + 'INNER JOIN "actor" ON actor.id= "actorFollow"."targetActorId" ' + + 'WHERE "actor"."id" = ' + actorIdNumber + ')' return { @@ -253,15 +253,14 @@ export class VideoChannelModel extends Model { limit: options.count, order: getSort(options.sort), where: { - id: { - [ Sequelize.Op.in ]: Sequelize.literal( - '(' + - 'SELECT id FROM "videoChannel" WHERE ' + - 'lower(immutable_unaccent("name")) % lower(immutable_unaccent(' + escapedSearch + ')) OR ' + - 'lower(immutable_unaccent("name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' + - ')' + [Sequelize.Op.or]: [ + Sequelize.literal( + 'lower(immutable_unaccent("VideoChannelModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))' + ), + Sequelize.literal( + 'lower(immutable_unaccent("VideoChannelModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))' ) - } + ] } } -- cgit v1.2.3