import { Model } from 'sequelize-typescript'
import { MUserAccountId, MUserId } from '@server/typings/models'
import validator from 'validator'
+import { exists } from '@server/helpers/custom-validators/misc'
export type BuildVideosQueryOptions = {
attributes?: string[]
}
if (options.languageOneOf) {
- replacements.languageOneOf = options.languageOneOf.filter(l => l && l !== '_unknown')
-
- let languagesQuery = '("video"."language" IN (:languageOneOf) OR '
+ const languages = options.languageOneOf.filter(l => l && l !== '_unknown')
+ const languagesQueryParts: string[] = []
+
+ if (languages.length !== 0) {
+ languagesQueryParts.push('"video"."language" IN (:languageOneOf)')
+ replacements.languageOneOf = languages
+
+ languagesQueryParts.push(
+ 'EXISTS (' +
+ ' SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' +
+ ' IN (' + createSafeIn(model, languages) + ') AND ' +
+ ' "videoCaption"."videoId" = "video"."id"' +
+ ')'
+ )
+ }
if (options.languageOneOf.includes('_unknown')) {
- languagesQuery += '"video"."language" IS NULL OR '
+ languagesQueryParts.push('"video"."language" IS NULL')
}
- and.push(
- languagesQuery +
- ' EXISTS (' +
- ' SELECT 1 FROM "videoCaption" WHERE "videoCaption"."language" ' +
- ' IN (' + createSafeIn(model, options.languageOneOf) + ') AND ' +
- ' "videoCaption"."videoId" = "video"."id"' +
- ' )' +
- ')'
- )
+ if (languagesQueryParts.length !== 0) {
+ and.push('(' + languagesQueryParts.join(' OR ') + ')')
+ }
}
// We don't exclude results in this if so if we do a count we don't need to add this complex clauses
let suffix = ''
let order = ''
if (options.isCount !== true) {
- const count = parseInt(options.count + '', 10)
- const start = parseInt(options.start + '', 10)
- order = buildOrder(model, options.sort)
+ if (exists(options.sort)) {
+ if (options.sort === '-originallyPublishedAt' || options.sort === 'originallyPublishedAt') {
+ attributes.push('COALESCE("video"."originallyPublishedAt", "video"."publishedAt") AS "publishedAtForOrder"')
+ }
+
+ order = buildOrder(model, options.sort)
+ suffix += `${order} `
+ }
- suffix = order + ' ' +
- 'LIMIT ' + count + ' ' +
- 'OFFSET ' + start
+ if (exists(options.count)) {
+ const count = parseInt(options.count + '', 10)
+ suffix += `LIMIT ${count} `
+ }
+
+ if (exists(options.start)) {
+ const start = parseInt(options.start + '', 10)
+ suffix += `OFFSET ${start} `
+ }
}
const cteString = cte.length !== 0
if (field.toLowerCase() === 'match') { // Search
firstSort = '"similarity"'
+ } else if (field === 'originallyPublishedAt') {
+ firstSort = '"publishedAtForOrder"'
} else if (field.includes('.')) {
firstSort = field
} else {