aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models
diff options
context:
space:
mode:
Diffstat (limited to 'server/models')
-rw-r--r--server/models/video/tag.ts6
-rw-r--r--server/models/video/video.ts10
2 files changed, 12 insertions, 4 deletions
diff --git a/server/models/video/tag.ts b/server/models/video/tag.ts
index b110f2a43..ed8df8b48 100644
--- a/server/models/video/tag.ts
+++ b/server/models/video/tag.ts
@@ -1,5 +1,5 @@
1import * as Bluebird from 'bluebird' 1import * as Bluebird from 'bluebird'
2import { QueryTypes, Transaction } from 'sequelize' 2import { fn, QueryTypes, Transaction, col } from 'sequelize'
3import { AllowNull, BelongsToMany, Column, CreatedAt, Is, Model, Table, UpdatedAt } from 'sequelize-typescript' 3import { AllowNull, BelongsToMany, Column, CreatedAt, Is, Model, Table, UpdatedAt } from 'sequelize-typescript'
4import { isVideoTagValid } from '../../helpers/custom-validators/videos' 4import { isVideoTagValid } from '../../helpers/custom-validators/videos'
5import { throwIfNotValid } from '../utils' 5import { throwIfNotValid } from '../utils'
@@ -15,6 +15,10 @@ import { MTag } from '@server/typings/models'
15 { 15 {
16 fields: [ 'name' ], 16 fields: [ 'name' ],
17 unique: true 17 unique: true
18 },
19 {
20 name: 'tag_lower_name',
21 fields: [ fn('lower', col('name')) ] as any // FIXME: typings
18 } 22 }
19 ] 23 ]
20}) 24})
diff --git a/server/models/video/video.ts b/server/models/video/video.ts
index 6a95f6ef7..6856dcd9f 100644
--- a/server/models/video/video.ts
+++ b/server/models/video/video.ts
@@ -468,13 +468,15 @@ export type AvailableForListIDsOptions = {
468 // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN() 468 // FIXME: issues with sequelize count when making a join on n:m relation, so we just make a IN()
469 if (options.tagsAllOf || options.tagsOneOf) { 469 if (options.tagsAllOf || options.tagsOneOf) {
470 if (options.tagsOneOf) { 470 if (options.tagsOneOf) {
471 const tagsOneOfLower = options.tagsOneOf.map(t => t.toLowerCase())
472
471 whereAnd.push({ 473 whereAnd.push({
472 id: { 474 id: {
473 [ Op.in ]: Sequelize.literal( 475 [ Op.in ]: Sequelize.literal(
474 '(' + 476 '(' +
475 'SELECT "videoId" FROM "videoTag" ' + 477 'SELECT "videoId" FROM "videoTag" ' +
476 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + 478 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
477 'WHERE "tag"."name" IN (' + createSafeIn(VideoModel, options.tagsOneOf) + ')' + 479 'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsOneOfLower) + ')' +
478 ')' 480 ')'
479 ) 481 )
480 } 482 }
@@ -482,14 +484,16 @@ export type AvailableForListIDsOptions = {
482 } 484 }
483 485
484 if (options.tagsAllOf) { 486 if (options.tagsAllOf) {
487 const tagsAllOfLower = options.tagsAllOf.map(t => t.toLowerCase())
488
485 whereAnd.push({ 489 whereAnd.push({
486 id: { 490 id: {
487 [ Op.in ]: Sequelize.literal( 491 [ Op.in ]: Sequelize.literal(
488 '(' + 492 '(' +
489 'SELECT "videoId" FROM "videoTag" ' + 493 'SELECT "videoId" FROM "videoTag" ' +
490 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' + 494 'INNER JOIN "tag" ON "tag"."id" = "videoTag"."tagId" ' +
491 'WHERE "tag"."name" IN (' + createSafeIn(VideoModel, options.tagsAllOf) + ')' + 495 'WHERE lower("tag"."name") IN (' + createSafeIn(VideoModel, tagsAllOfLower) + ')' +
492 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + options.tagsAllOf.length + 496 'GROUP BY "videoTag"."videoId" HAVING COUNT(*) = ' + tagsAllOfLower.length +
493 ')' 497 ')'
494 ) 498 )
495 } 499 }