- return query
- },
- [ ScopeNames.AVAILABLE_FOR_LIST_IDS ]: (options: AvailableForListIDsOptions) => {
- const whereAnd = options.baseWhere ? options.baseWhere : []
-
- const query: FindOptions = {
- raw: true,
- include: []
- }
-
- const attributesType = options.attributesType || 'id'
-
- if (attributesType === 'id') query.attributes = [ 'id' ]
- else if (attributesType === 'none') query.attributes = [ ]
-
- whereAnd.push({
- id: {
- [ Op.notIn ]: Sequelize.literal(
- '(SELECT "videoBlacklist"."videoId" FROM "videoBlacklist")'
- )
- }
- })
-
- if (options.serverAccountId) {
- whereAnd.push({
- channelId: {
- [ Op.notIn ]: Sequelize.literal(
- '(' +
- 'SELECT id FROM "videoChannel" WHERE "accountId" IN (' +
- buildBlockedAccountSQL(options.serverAccountId, options.user ? options.user.Account.id : undefined) +
- ')' +
- ')'
- )
- }
- })
- }
-
- // 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
- [ Op.or ]: [
- {
- state: VideoState.PUBLISHED
- },
- {
- [ Op.and ]: {
- state: VideoState.TO_TRANSCODE,
- waitTranscoding: false
- }
- }
- ]
- }
-
- whereAnd.push(privacyWhere)
- }
-
- if (options.videoPlaylistId) {
- query.include.push({
- attributes: [],
- model: VideoPlaylistElementModel.unscoped(),
- required: true,
- where: {
- videoPlaylistId: options.videoPlaylistId
- }
- })
-
- query.subQuery = false
- }
-
- if (options.filter || options.accountId || options.videoChannelId) {
- const videoChannelInclude: IncludeOptions = {
- attributes: [],
- model: VideoChannelModel.unscoped(),
- required: true
- }
-
- if (options.videoChannelId) {
- videoChannelInclude.where = {
- id: options.videoChannelId
- }
- }
-
- if (options.filter || options.accountId) {
- const accountInclude: IncludeOptions = {
- 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)
- whereAnd.push({
- id: {
- [ 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) {
- whereAnd.push({
- id: {
- [ 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) {
- if (options.tagsOneOf) {
- const tagsOneOfLower = options.tagsOneOf.map(t => t.toLowerCase())
-
- whereAnd.push({
- id: {
- [ Op.in ]: Sequelize.literal(
- '(' +
- 'SELECT "videoId" FROM "videoTag" ' +
- 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
- 'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsOneOfLower) + ')' +
- ')'
- )
- }
- })
- }
-
- if (options.tagsAllOf) {
- const tagsAllOfLower = options.tagsAllOf.map(t => t.toLowerCase())
-
- whereAnd.push({
- id: {
- [ Op.in ]: Sequelize.literal(
- '(' +
- 'SELECT "videoId" FROM "videoTag" ' +
- 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
- 'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsAllOfLower) + ')' +
- 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length +
- ')'
- )
- }
- })
- }
- }
-
- if (options.nsfw === true || options.nsfw === false) {
- whereAnd.push({ nsfw: options.nsfw })
- }
-
- if (options.categoryOneOf) {
- whereAnd.push({
- category: {
- [ Op.or ]: options.categoryOneOf
- }
- })
- }
-
- if (options.licenceOneOf) {
- whereAnd.push({
- licence: {
- [ Op.or ]: options.licenceOneOf
- }
- })
- }
-
- if (options.languageOneOf) {
- let videoLanguages = options.languageOneOf
- if (options.languageOneOf.find(l => l === '_unknown')) {
- videoLanguages = videoLanguages.concat([ null ])
- }
-
- whereAnd.push({
- [Op.or]: [
- {
- language: {
- [ Op.or ]: videoLanguages
- }
- },
- {
- id: {
- [ Op.in ]: Sequelize.literal(
- '(' +
- 'SELECT "videoId" FROM "videoCaption" ' +
- 'WHERE "language" IN (' + createSafeIn(VideoModel, options.languageOneOf) + ') ' +
- ')'
- )
- }
- }
- ]
- })
- }
-
- if (options.trendingDays) {
- query.include.push(VideoModel.buildTrendingQuery(options.trendingDays))
-
- query.subQuery = false
- }
-
- if (options.historyOfUser) {
- query.include.push({
- model: UserVideoHistoryModel,
- required: true,
- where: {
- userId: options.historyOfUser.id
- }
- })
-
- // Even if the relation is n:m, we know that a user only have 0..1 video history
- // So we won't have multiple rows for the same video
- // Without this, we would not be able to sort on "updatedAt" column of UserVideoHistoryModel
- query.subQuery = false
- }
-
- query.where = {
- [ Op.and ]: whereAnd
- }