aboutsummaryrefslogtreecommitdiffhomepage
path: root/server/models/utils.ts
diff options
context:
space:
mode:
Diffstat (limited to 'server/models/utils.ts')
-rw-r--r--server/models/utils.ts52
1 files changed, 50 insertions, 2 deletions
diff --git a/server/models/utils.ts b/server/models/utils.ts
index 59ce83c16..49d32c24f 100644
--- a/server/models/utils.ts
+++ b/server/models/utils.ts
@@ -1,6 +1,8 @@
1// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ] 1// Translate for example "-name" to [ [ 'name', 'DESC' ], [ 'id', 'ASC' ] ]
2import { Sequelize } from 'sequelize-typescript'
3
2function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) { 4function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) {
3 let field: string 5 let field: any
4 let direction: 'ASC' | 'DESC' 6 let direction: 'ASC' | 'DESC'
5 7
6 if (value.substring(0, 1) === '-') { 8 if (value.substring(0, 1) === '-') {
@@ -11,6 +13,9 @@ function getSort (value: string, lastSort: string[] = [ 'id', 'ASC' ]) {
11 field = value 13 field = value
12 } 14 }
13 15
16 // Alias
17 if (field.toLowerCase() === 'bestmatch') field = Sequelize.col('similarity')
18
14 return [ [ field, direction ], lastSort ] 19 return [ [ field, direction ], lastSort ]
15} 20}
16 21
@@ -27,10 +32,53 @@ function throwIfNotValid (value: any, validator: (value: any) => boolean, fieldN
27 } 32 }
28} 33}
29 34
35function buildTrigramSearchIndex (indexName: string, attribute: string) {
36 return {
37 name: indexName,
38 fields: [ Sequelize.literal('lower(immutable_unaccent(' + attribute + '))') as any ],
39 using: 'gin',
40 operator: 'gin_trgm_ops'
41 }
42}
43
44function createSimilarityAttribute (col: string, value: string) {
45 return Sequelize.fn(
46 'similarity',
47
48 searchTrigramNormalizeCol(col),
49
50 searchTrigramNormalizeValue(value)
51 )
52}
53
54function createSearchTrigramQuery (col: string, value: string) {
55 return {
56 [ Sequelize.Op.or ]: [
57 // FIXME: use word_similarity instead of just similarity?
58 Sequelize.where(searchTrigramNormalizeCol(col), ' % ', searchTrigramNormalizeValue(value)),
59
60 Sequelize.where(searchTrigramNormalizeCol(col), ' LIKE ', searchTrigramNormalizeValue(`%${value}%`))
61 ]
62 }
63}
64
30// --------------------------------------------------------------------------- 65// ---------------------------------------------------------------------------
31 66
32export { 67export {
33 getSort, 68 getSort,
34 getSortOnModel, 69 getSortOnModel,
35 throwIfNotValid 70 createSimilarityAttribute,
71 throwIfNotValid,
72 buildTrigramSearchIndex,
73 createSearchTrigramQuery
74}
75
76// ---------------------------------------------------------------------------
77
78function searchTrigramNormalizeValue (value: string) {
79 return Sequelize.fn('lower', Sequelize.fn('unaccent', value))
80}
81
82function searchTrigramNormalizeCol (col: string) {
83 return Sequelize.fn('lower', Sequelize.fn('immutable_unaccent', Sequelize.col(col)))
36} 84}