Skip to content

Commit b23ac3e

Browse files
jfgirardjfgirard
committed
add match operator for text search
1 parent 28b1ca3 commit b23ac3e

7 files changed

Lines changed: 80 additions & 1 deletion

File tree

‎lib/dialect/mssql.js‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -26,6 +26,14 @@ Mssql.prototype._getParameterPlaceholder = function(index, value) {
2626
};
2727

2828
Mssql.prototype.visitBinary = function(binary) {
29+
if(binary.operator === '@@'){
30+
var self = this;
31+
var text = '(CONTAINS (' + this.visit(binary.left) + ', ';
32+
text += this.visit(binary.right);
33+
text += '))';
34+
return [text];
35+
}
36+
2937
if (!isRightSideArray(binary)){
3038
return Mssql.super_.prototype.visitBinary.call(this, binary);
3139
}

‎lib/dialect/mysql.js‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -76,4 +76,15 @@ Mysql.prototype.visitIndexes = function(node) {
7676
return "SHOW INDEX FROM " + tableName;
7777
};
7878

79+
Mysql.prototype.visitBinary = function(binary) {
80+
if (binary.operator === '@@') {
81+
var self = this;
82+
var text = '(MATCH ' + this.visit(binary.left) + ' AGAINST ';
83+
text += this.visit(binary.right);
84+
text += ')';
85+
return [text];
86+
}
87+
return Mysql.super_.prototype.visitBinary.call(this, binary);
88+
}
89+
7990
module.exports = Mysql;

‎lib/dialect/sqlite.js‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -70,4 +70,14 @@ Sqlite.prototype.visitRestrict = function() {
7070
throw new Error('Sqlite do not support RESTRICT in DROP TABLE');
7171
};
7272

73+
Sqlite.prototype.visitBinary = function(binary) {
74+
if(binary.operator === '@@'){
75+
binary.operator = 'MATCH';
76+
var ret = Sqlite.super_.prototype.visitBinary.call(this, binary);
77+
binary.operator = '@@';
78+
return ret;
79+
}
80+
return Sqlite.super_.prototype.visitBinary.call(this, binary);
81+
}
82+
7383
module.exports = Sqlite;

‎lib/functions.js‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -50,7 +50,10 @@ var scalarFunctions = [
5050
// hstore function available to Postgres
5151
var hstoreFunction = 'HSTORE';
5252

53-
var standardFunctionNames = aggregateFunctions.concat(scalarFunctions).concat(hstoreFunction);
53+
//text search functions available to Postgres
54+
var textsearchFunctions = ['TS_RANK','TS_RANK_CD', 'PLAINTO_TSQUERY', 'TO_TSQUERY', 'TO_TSVECTOR', 'SETWEIGHT'];
55+
56+
var standardFunctionNames = aggregateFunctions.concat(scalarFunctions).concat(hstoreFunction).concat(textsearchFunctions);
5457

5558
// creates a hash of standard functions for a sql instance
5659
var getStandardFunctions = function(sql) {

‎lib/node/valueExpression.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -150,6 +150,7 @@ var ValueExpressionMixin = function() {
150150
notLike : binaryMethod('NOT LIKE'),
151151
ilike : binaryMethod('ILIKE'),
152152
notIlike : binaryMethod('NOT ILIKE'),
153+
match : binaryMethod('@@'),
153154
in : inMethod,
154155
notIn : notInMethod,
155156
between : ternaryMethod('BETWEEN', 'AND'),

‎test/binary-clause-tests.js‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -38,4 +38,5 @@ test('operators', function() {
3838
assert.equal(Foo.baz.rlike(1).operator, 'RLIKE');
3939
assert.equal(Foo.baz.ilike('asdf').operator, 'ILIKE');
4040
assert.equal(Foo.baz.notIlike('asdf').operator, 'NOT ILIKE');
41+
assert.equal(Foo.baz.match('asdf').operator, '@@');
4142
});

‎test/dialects/matches-test.js‎

Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
'use strict';
2+
3+
var Harness = require('./support');
4+
var post = Harness.definePostTable();
5+
var customerAlias = Harness.defineCustomerAliasTable();
6+
var sql = require(__dirname + '/../../lib').setDialect('postgres');
7+
8+
//Postgres needs the to_tsquery function to use with @@ operator
9+
Harness.test({
10+
query: post.select(post.star()).where(post.content.match(sql.functions.TO_TSQUERY('hello'))),
11+
pg: {
12+
text : 'SELECT "post".* FROM "post" WHERE ("post"."content" @@ TO_TSQUERY($1))',
13+
string: 'SELECT "post".* FROM "post" WHERE ("post"."content" @@ TO_TSQUERY(\'hello\'))'
14+
},
15+
params: ['hello']
16+
});
17+
18+
19+
Harness.test({
20+
query: post.select(post.star()).where(post.content.match('hello')),
21+
sqlite: {
22+
text : 'SELECT "post".* FROM "post" WHERE ("post"."content" MATCH $1)',
23+
string: 'SELECT "post".* FROM "post" WHERE ("post"."content" MATCH \'hello\')'
24+
},
25+
mysql: {
26+
text : 'SELECT `post`.* FROM `post` WHERE (MATCH `post`.`content` AGAINST ?)',
27+
string: 'SELECT `post`.* FROM `post` WHERE (MATCH `post`.`content` AGAINST \'hello\')'
28+
},
29+
mssql: {
30+
text : 'SELECT [post].* FROM [post] WHERE (CONTAINS ([post].[content], @1))',
31+
string: 'SELECT [post].* FROM [post] WHERE (CONTAINS ([post].[content], \'hello\'))'
32+
},
33+
params: ['hello']
34+
});
35+
36+
//matches, ordered by best rank first
37+
Harness.test({
38+
query: post.select(post.id, sql.functions.TS_RANK_CD(post.content, sql.functions.TO_TSQUERY('hello')).as('rank')).
39+
where(post.content.match(sql.functions.TO_TSQUERY('hello'))).order(sql.functions.TS_RANK_CD(post.content, sql.functions.TO_TSQUERY('hello')).descending()),
40+
pg: {
41+
text : 'SELECT "post"."id", TS_RANK_CD("post"."content", TO_TSQUERY($1)) AS "rank" FROM "post" WHERE ("post"."content" @@ TO_TSQUERY($2)) ORDER BY TS_RANK_CD("post"."content", TO_TSQUERY($3)) DESC',
42+
string: 'SELECT "post"."id", TS_RANK_CD("post"."content", TO_TSQUERY(\'hello\')) AS "rank" FROM "post" WHERE ("post"."content" @@ TO_TSQUERY(\'hello\')) ORDER BY TS_RANK_CD("post"."content", TO_TSQUERY(\'hello\')) DESC'
43+
},
44+
params: ['hello','hello','hello']
45+
});

0 commit comments

Comments
 (0)