From 37a44fc915eef2140e22ceb96aba6b6eb2509007 Mon Sep 17 00:00:00 2001
From: Chocobozzz <me@florianbigard.com>
Date: Thu, 17 Jun 2021 16:02:38 +0200
Subject: Add ability to search playlists

---
 .../sql/shared/abstract-videos-query-builder.ts    |  2 +-
 server/models/video/video-channel.ts               |  4 +-
 server/models/video/video-playlist.ts              | 84 ++++++++++++++++++++--
 3 files changed, 80 insertions(+), 10 deletions(-)

(limited to 'server/models')

diff --git a/server/models/video/sql/shared/abstract-videos-query-builder.ts b/server/models/video/sql/shared/abstract-videos-query-builder.ts
index 3a1ee5b1f..09776bcb0 100644
--- a/server/models/video/sql/shared/abstract-videos-query-builder.ts
+++ b/server/models/video/sql/shared/abstract-videos-query-builder.ts
@@ -18,7 +18,7 @@ export class AbstractVideosQueryBuilder {
       logging: options.logging,
       replacements: this.replacements,
       type: QueryTypes.SELECT as QueryTypes.SELECT,
-      next: false
+      nest: false
     }
 
     return this.sequelize.query<any>(this.query, queryOptions)
diff --git a/server/models/video/video-channel.ts b/server/models/video/video-channel.ts
index 33749ea70..f84b85290 100644
--- a/server/models/video/video-channel.ts
+++ b/server/models/video/video-channel.ts
@@ -434,8 +434,8 @@ ON              "Account->Actor"."serverId" = "Account->Actor->Server"."id"`
     sort: string
   }) {
     const attributesInclude = []
-    const escapedSearch = VideoModel.sequelize.escape(options.search)
-    const escapedLikeSearch = VideoModel.sequelize.escape('%' + options.search + '%')
+    const escapedSearch = VideoChannelModel.sequelize.escape(options.search)
+    const escapedLikeSearch = VideoChannelModel.sequelize.escape('%' + options.search + '%')
     attributesInclude.push(createSimilarityAttribute('VideoChannelModel.name', options.search))
 
     const query = {
diff --git a/server/models/video/video-playlist.ts b/server/models/video/video-playlist.ts
index 1a05f8d42..7aa6b6c6e 100644
--- a/server/models/video/video-playlist.ts
+++ b/server/models/video/video-playlist.ts
@@ -1,5 +1,5 @@
 import { join } from 'path'
-import { FindOptions, literal, Op, ScopeOptions, Transaction, WhereOptions } from 'sequelize'
+import { FindOptions, literal, Op, ScopeOptions, Sequelize, Transaction, WhereOptions } from 'sequelize'
 import {
   AllowNull,
   BelongsTo,
@@ -53,7 +53,15 @@ import {
 } from '../../types/models/video/video-playlist'
 import { AccountModel, ScopeNames as AccountScopeNames, SummaryOptions } from '../account/account'
 import { ActorModel } from '../actor/actor'
-import { buildServerIdsFollowedBy, buildWhereIdOrUUID, getPlaylistSort, isOutdated, throwIfNotValid } from '../utils'
+import {
+  buildServerIdsFollowedBy,
+  buildTrigramSearchIndex,
+  buildWhereIdOrUUID,
+  createSimilarityAttribute,
+  getPlaylistSort,
+  isOutdated,
+  throwIfNotValid
+} from '../utils'
 import { ThumbnailModel } from './thumbnail'
 import { ScopeNames as VideoChannelScopeNames, VideoChannelModel } from './video-channel'
 import { VideoPlaylistElementModel } from './video-playlist-element'
@@ -74,6 +82,11 @@ type AvailableForListOptions = {
   videoChannelId?: number
   listMyPlaylists?: boolean
   search?: string
+  withVideos?: boolean
+}
+
+function getVideoLengthSelect () {
+  return 'SELECT COUNT("id") FROM "videoPlaylistElement" WHERE "videoPlaylistId" = "VideoPlaylistModel"."id"'
 }
 
 @Scopes(() => ({
@@ -89,7 +102,7 @@ type AvailableForListOptions = {
     attributes: {
       include: [
         [
-          literal('(SELECT COUNT("id") FROM "videoPlaylistElement" WHERE "videoPlaylistId" = "VideoPlaylistModel"."id")'),
+          literal(`(${getVideoLengthSelect()})`),
           'videosLength'
         ]
       ]
@@ -178,11 +191,28 @@ type AvailableForListOptions = {
       })
     }
 
+    if (options.withVideos === true) {
+      whereAnd.push(
+        literal(`(${getVideoLengthSelect()}) != 0`)
+      )
+    }
+
+    const attributesInclude = []
+
     if (options.search) {
+      const escapedSearch = VideoPlaylistModel.sequelize.escape(options.search)
+      const escapedLikeSearch = VideoPlaylistModel.sequelize.escape('%' + options.search + '%')
+      attributesInclude.push(createSimilarityAttribute('VideoPlaylistModel.name', options.search))
+
       whereAnd.push({
-        name: {
-          [Op.iLike]: '%' + options.search + '%'
-        }
+        [Op.or]: [
+          Sequelize.literal(
+            'lower(immutable_unaccent("VideoPlaylistModel"."name")) % lower(immutable_unaccent(' + escapedSearch + '))'
+          ),
+          Sequelize.literal(
+            'lower(immutable_unaccent("VideoPlaylistModel"."name")) LIKE lower(immutable_unaccent(' + escapedLikeSearch + '))'
+          )
+        ]
       })
     }
 
@@ -191,6 +221,9 @@ type AvailableForListOptions = {
     }
 
     return {
+      attributes: {
+        include: attributesInclude
+      },
       where,
       include: [
         {
@@ -211,6 +244,8 @@ type AvailableForListOptions = {
 @Table({
   tableName: 'videoPlaylist',
   indexes: [
+    buildTrigramSearchIndex('video_playlist_name_trigram', 'name'),
+
     {
       fields: [ 'ownerAccountId' ]
     },
@@ -314,6 +349,7 @@ export class VideoPlaylistModel extends Model<Partial<AttributesOnly<VideoPlayli
     videoChannelId?: number
     listMyPlaylists?: boolean
     search?: string
+    withVideos?: boolean // false by default
   }) {
     const query = {
       offset: options.start,
@@ -331,7 +367,8 @@ export class VideoPlaylistModel extends Model<Partial<AttributesOnly<VideoPlayli
             accountId: options.accountId,
             videoChannelId: options.videoChannelId,
             listMyPlaylists: options.listMyPlaylists,
-            search: options.search
+            search: options.search,
+            withVideos: options.withVideos || false
           } as AvailableForListOptions
         ]
       },
@@ -347,6 +384,21 @@ export class VideoPlaylistModel extends Model<Partial<AttributesOnly<VideoPlayli
       })
   }
 
+  static searchForApi (options: {
+    followerActorId: number
+    start: number
+    count: number
+    sort: string
+    search?: string
+  }) {
+    return VideoPlaylistModel.listForApi({
+      ...options,
+      type: VideoPlaylistType.REGULAR,
+      listMyPlaylists: false,
+      withVideos: true
+    })
+  }
+
   static listPublicUrlsOfForAP (options: { account?: MAccountId, channel?: MChannelId }, start: number, count: number) {
     const where = {
       privacy: VideoPlaylistPrivacy.PUBLIC
@@ -445,6 +497,18 @@ export class VideoPlaylistModel extends Model<Partial<AttributesOnly<VideoPlayli
     return VideoPlaylistModel.scope([ ScopeNames.WITH_ACCOUNT, ScopeNames.WITH_THUMBNAIL ]).findOne(query)
   }
 
+  static loadByUrlWithAccountAndChannelSummary (url: string): Promise<MVideoPlaylistFullSummary> {
+    const query = {
+      where: {
+        url
+      }
+    }
+
+    return VideoPlaylistModel
+      .scope([ ScopeNames.WITH_ACCOUNT_AND_CHANNEL_SUMMARY, ScopeNames.WITH_VIDEOS_LENGTH, ScopeNames.WITH_THUMBNAIL ])
+      .findOne(query)
+  }
+
   static getPrivacyLabel (privacy: VideoPlaylistPrivacy) {
     return VIDEO_PLAYLIST_PRIVACIES[privacy] || 'Unknown'
   }
@@ -535,6 +599,10 @@ export class VideoPlaylistModel extends Model<Partial<AttributesOnly<VideoPlayli
     return setAsUpdated('videoPlaylist', this.id)
   }
 
+  setVideosLength (videosLength: number) {
+    this.set('videosLength' as any, videosLength, { raw: true })
+  }
+
   isOwned () {
     return this.OwnerAccount.isOwned()
   }
@@ -551,6 +619,8 @@ export class VideoPlaylistModel extends Model<Partial<AttributesOnly<VideoPlayli
       uuid: this.uuid,
       isLocal: this.isOwned(),
 
+      url: this.url,
+
       displayName: this.name,
       description: this.description,
       privacy: {
-- 
cgit v1.2.3