I am looking for a way to search for a string with spaces in postgres using sequelize.
I have a database named library
with a books
table. This has a title
and author
column in there.I have tried using iLike but this wasn't very fruitful, the search is returning nothing once I include a space.
So I used this article and this too and came up with the following:
// migration file - seems to work fine
"use strict";
module.exports = {
async up(queryInterface, Sequelize) {
var sequelize = queryInterface.sequelize;
var searchFields = ["title", "author"];
var vectorName = "vector";
var tableName = "books";
await sequelize
.query(`ALTER TABLE ${tableName} ADD COLUMN ${vectorName} TSVECTOR;`)
.then(function () {
console.log("Column added: Adding updating values");
return sequelize
.query(
`UPDATE ${tableName} SET ${vectorName} = to_tsvector('english', ${searchFields.join(
" || ' ' || "
)});`
)
.catch(console.log);
})
.then(function () {
console.log("Values added: Creating Index");
return sequelize
.query(
`CREATE INDEX ${tableName}_search ON ${tableName} USING gin(${vectorName});`
)
.catch(console.log);
})
.then(function () {
console.log("Index created: Adding trigger");
return sequelize
.query(
`CREATE TRIGGER ${tableName}_vector_update
BEFORE INSERT OR UPDATE ON ${tableName}
FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger(${vectorName}, 'pg_catalog.english', ${searchFields.join(
", "
)});`
)
.catch(console.log);
})
.then(function () {
console.log("Everything worked!");
})
.catch(console.log);
},
async down(queryInterface, Sequelize) {
var sequelize = queryInterface.sequelize;
var searchFields = ["title", "author"];
var vectorName = "vector";
var tableName = "books";
await sequelize
.query(`DROP TRIGGER ${tableName}_vector_update ON ${tableName};`)
.then(function () {
console.log("removed trigger");
return sequelize
.query(`DROP INDEX ${tableName}_search;`)
.catch(console.log);
})
.then(function () {
console.log("removed index");
return sequelize
.query(`ALTER TABLE ${tableName} DROP COLUMN ${vectorName};`)
.catch(console.log);
})
.then(function () {
console.log("removed column");
})
.catch(console.log);
},
};
The books table is created, seeds and migration runs ok and populate the vector
column. To make the query I initially had, when using iLike
:
exports.Suggestbooks = class Suggestbooks {
constructor(options, app) {
this.app = app;
this.options = options || {};
}
async find(data, params) {
const query = data?.query
const results = await this.app.service("books").find({
query: {
...query,
$sort: {
updatedAt: -1,
},
},
})
.catch((err) => {
console.log({hj4jkl6j5lll4: err})
});
return results;
}
};
So now how do I make the query?
To anyone curious, here's how I finally solved this
const Sequelize = require("sequelize");
/* eslint-disable no-unused-vars */
exports.Suggestbooks = class Suggestbooks {
constructor(options, app) {
this.app = app;
this.options = options || {};
}
async find(data, params) {
const query = data?.query?.title;
const sequelizeClient = this.app.get("sequelizeClient");
return new Promise(async (resolve, reject) => {
await sequelizeClient
.query(`SELECT * FROM books WHERE vector @@ to_tsquery('${query}:*')`)
.then(async (res) => {
const newMap = await res[0]?.reduce(async (init, curr) => {
let newArray = await init;
const books = await this.app
.service("books")
.find({
query: {
id: curr.id,
deleted: false,
allowed: true,
$sort: {
updatedAt: -1,
},
},
})
.catch((err) => {
console.log({ hj4jkl6j5lll4: err });
});
if (!!books) newArray.push(books.data[0]);
return init;
}, Promise.resolve([]));
resolve(newMap);
});
});
}
};