postgresqlsequelize.jsfeathersjsfeathers-sequelize

Search for a string with spaces in sequelize for postgres


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?


Solution

  • 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);
            });
        });
      }
    };