matchTsquery - nodef/extra-sql GitHub Wiki

Generates SQL query for matching words with tsquery.

sql.matchTsquery(table, words, [tsvector], [options])
// table:  table name
// words:  match words
// tsvector: tsvector ("tsvector")
// options:  options {columns, order, limit, normalization}
// .columns: select columns (*)
// .order:   order rows? (false)
// .limit:   limit rows (0 => no)
// .normalization: rank normalization (0 => ignores the document length)

const sql = require('extra-sql');

sql.matchTsquery('columns', ['total', 'fat']);
// SELECT *, '2'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total fat') UNION ALL
// SELECT *, '1'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total');
 
sql.matchTsquery('columns', ['total', 'fat'], '"tsvector"', {columns: '"code"'});
// SELECT "code", '2'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total fat') UNION ALL
// SELECT "code", '1'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total');
 
sql.matchTsquery('columns', ['total', 'fat'], '"tsvector"', {order: true, limit: 1, normalization: 2});
// SELECT *, '2'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total fat') ORDER BY ts_rank("tsvector", plainto_tsquery('total fat'), 2) DESC UNION ALL
// SELECT *, '1'::INT AS "matchTsquery" FROM "columns" WHERE "tsvector" @@ plainto_tsquery('total') ORDER BY ts_rank("tsvector", plainto_tsquery('total'), 2) DESC LIMIT 1;