@sql-extra/matchtsquery v0.1.1

@sql-extra/matchtsquery

Generate SQL query for matching words with tsquery.

const matchTsquery = require('@sql-extra/matchtsquery');
// matchTsquery(<table>, <words>, [tsvector], [options])
// -> sql command

// options: {
//   columns: '*',  // select columns: all
//   order: false,  // order rows: no
//   limit: 0,      // limit rows: no
//   normalization: 0,  // rank normalization: ignores the document length
// }


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');

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');

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;