aboutsummaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
-rw-r--r--server/models/video/tag.ts6
-rw-r--r--server/models/video/video.ts10
-rw-r--r--server/tests/api/search/search-videos.ts8
3 files changed, 16 insertions, 8 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 }
diff --git a/server/tests/api/search/search-videos.ts b/server/tests/api/search/search-videos.ts
index c06200ffe..a3e05156b 100644
--- a/server/tests/api/search/search-videos.ts
+++ b/server/tests/api/search/search-videos.ts
@@ -206,7 +206,7 @@ describe('Test videos search', function () {
206 const query = { 206 const query = {
207 search: '9999', 207 search: '9999',
208 categoryOneOf: [ 1 ], 208 categoryOneOf: [ 1 ],
209 tagsOneOf: [ 'aaaa', 'ffff' ] 209 tagsOneOf: [ 'aAaa', 'ffff' ]
210 } 210 }
211 const res1 = await advancedVideosSearch(server.url, query) 211 const res1 = await advancedVideosSearch(server.url, query)
212 expect(res1.body.total).to.equal(2) 212 expect(res1.body.total).to.equal(2)
@@ -219,15 +219,15 @@ describe('Test videos search', function () {
219 const query = { 219 const query = {
220 search: '9999', 220 search: '9999',
221 categoryOneOf: [ 1 ], 221 categoryOneOf: [ 1 ],
222 tagsAllOf: [ 'cccc' ] 222 tagsAllOf: [ 'CCcc' ]
223 } 223 }
224 const res1 = await advancedVideosSearch(server.url, query) 224 const res1 = await advancedVideosSearch(server.url, query)
225 expect(res1.body.total).to.equal(2) 225 expect(res1.body.total).to.equal(2)
226 226
227 const res2 = await advancedVideosSearch(server.url, immutableAssign(query, { tagsAllOf: [ 'blabla' ] })) 227 const res2 = await advancedVideosSearch(server.url, immutableAssign(query, { tagsAllOf: [ 'blAbla' ] }))
228 expect(res2.body.total).to.equal(0) 228 expect(res2.body.total).to.equal(0)
229 229
230 const res3 = await advancedVideosSearch(server.url, immutableAssign(query, { tagsAllOf: [ 'bbbb', 'cccc' ] })) 230 const res3 = await advancedVideosSearch(server.url, immutableAssign(query, { tagsAllOf: [ 'bbbb', 'CCCC' ] }))
231 expect(res3.body.total).to.equal(1) 231 expect(res3.body.total).to.equal(1)
232 }) 232 })
233 233