+ return query
+ },
+ [ ScopeNames.AVAILABLE_FOR_LIST_IDS ]: (options: AvailableForListIDsOptions) => {
+ const query: IFindOptions<VideoModel> = {
+ raw: true,
+ attributes: [ 'id' ],
+ where: {
+ id: {
+ [ Sequelize.Op.and ]: [
+ {
+ [ Sequelize.Op.notIn ]: Sequelize.literal(
+ '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
+ )
+ }
+ ]
+ },
+ channelId: {
+ [ Sequelize.Op.notIn ]: Sequelize.literal(
+ '(' +
+ 'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
+ buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
+ ')' +
+ ')'
+ )
+ }
+ },
+ include: []
+ }
+
+ // Only list public/published videos
+ if (!options.filter || options.filter !== 'all-local') {
+ const privacyWhere = {
+ // Always list public videos
+ privacy: VideoPrivacy.PUBLIC,
+ // Always list published videos, or videos that are being transcoded but on which we don't want to wait for transcoding
+ [ Sequelize.Op.or ]: [
+ {
+ state: VideoState.PUBLISHED
+ },
+ {
+ [ Sequelize.Op.and ]: {
+ state: VideoState.TO_TRANSCODE,
+ waitTranscoding: false
+ }
+ }
+ ]
+ }
+
+ Object.assign(query.where, privacyWhere)
+ }
+
+ if (options.filter || options.accountId || options.videoChannelId) {
+ const videoChannelInclude: IIncludeOptions = {
+ attributes: [],
+ model: VideoChannelModel.unscoped(),
+ required: true
+ }
+
+ if (options.videoChannelId) {
+ videoChannelInclude.where = {
+ id: options.videoChannelId
+ }
+ }
+
+ if (options.filter || options.accountId) {
+ const accountInclude: IIncludeOptions = {
+ attributes: [],
+ model: AccountModel.unscoped(),
+ required: true
+ }
+
+ if (options.filter) {
+ accountInclude.include = [
+ {
+ attributes: [],
+ model: ActorModel.unscoped(),
+ required: true,
+ where: VideoModel.buildActorWhereWithFilter(options.filter)
+ }
+ ]
+ }
+
+ if (options.accountId) {
+ accountInclude.where = { id: options.accountId }
+ }
+
+ videoChannelInclude.include = [ accountInclude ]
+ }
+
+ query.include.push(videoChannelInclude)
+ }
+
+ if (options.followerActorId) {
+ let localVideosReq = ''
+ if (options.includeLocalVideos === true) {
+ localVideosReq = ' UNION ALL ' +
+ 'SELECT "video"."id" AS "id" FROM "video" ' +
+ 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+ 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
+ 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
+ 'WHERE "actor"."serverId" IS NULL'
+ }
+
+ // Force actorId to be a number to avoid SQL injections
+ const actorIdNumber = parseInt(options.followerActorId.toString(), 10)
+ query.where[ 'id' ][ Sequelize.Op.and ].push({
+ [ Sequelize.Op.in ]: Sequelize.literal(
+ '(' +
+ 'SELECT "videoShare"."videoId" AS "id" FROM "videoShare" ' +
+ 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "videoShare"."actorId" ' +
+ 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
+ ' UNION ALL ' +
+ 'SELECT "video"."id" AS "id" FROM "video" ' +
+ 'INNER JOIN "videoChannel" ON "videoChannel"."id" = "video"."channelId" ' +
+ 'INNER JOIN "account" ON "account"."id" = "videoChannel"."accountId" ' +
+ 'INNER JOIN "actor" ON "account"."actorId" = "actor"."id" ' +
+ 'INNER JOIN "actorFollow" ON "actorFollow"."targetActorId" = "actor"."id" ' +
+ 'WHERE "actorFollow"."actorId" = ' + actorIdNumber +
+ localVideosReq +
+ ')'
+ )
+ })
+ }
+
+ if (options.withFiles === true) {
+ query.where[ 'id' ][ Sequelize.Op.and ].push({
+ [ Sequelize.Op.in ]: Sequelize.literal(
+ '(SELECT "videoId" FROM "videoFile")'
+ )
+ })
+ }
+
+ // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN()
+ if (options.tagsAllOf || options.tagsOneOf) {
+ const createTagsIn = (tags: string[]) => {
+ return tags.map(t => VideoModel.sequelize.escape(t))
+ .join(', ')
+ }
+
+ if (options.tagsOneOf) {
+ query.where[ 'id' ][ Sequelize.Op.and ].push({
+ [ Sequelize.Op.in ]: Sequelize.literal(
+ '(' +
+ 'SELECT "videoId" FROM "videoTag" ' +
+ 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
+ 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsOneOf) + ')' +
+ ')'
+ )
+ })
+ }
+
+ if (options.tagsAllOf) {
+ query.where[ 'id' ][ Sequelize.Op.and ].push({
+ [ Sequelize.Op.in ]: Sequelize.literal(
+ '(' +
+ 'SELECT "videoId" FROM "videoTag" ' +
+ 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
+ 'WHERE "tag"."name" IN (' + createTagsIn(options.tagsAllOf) + ')' +
+ 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length +
+ ')'
+ )
+ })
+ }
+ }
+
+ if (options.nsfw === true || options.nsfw === false) {
+ query.where[ 'nsfw' ] = options.nsfw
+ }
+
+ if (options.categoryOneOf) {
+ query.where[ 'category' ] = {
+ [ Sequelize.Op.or ]: options.categoryOneOf
+ }