Feathers-vuex: How to use $regexp and $iRegexp in a query

[Adam-0-Moore]

I'm trying to search an ltree column in postgresql, and followed this advice https://github.com/sequelize/sequelize/issues/11629

Steps to reproduce

I've whitelisted $regex and $iRegex in feathers-client.js

const {
  makeServicePlugin, makeAuthPlugin, BaseModel, models, clients, FeathersVuex,
} = feathersVuex(feathersClient, {
  idField: 'id',
  whitelist: ['$regexp', '$iRegexp', '$options']
});

I also tried $regex and iRegex

I used the following query in the component

computed: {
    findQuery() {
      return {
        $sort: {
          id: 1
        },
        $limit: 100,
        path: {
          $regexp: 'root.*'
        }
      }
    }
  },

  methods: {
    ...mapActions('data', {
      findItems: 'find',
    })
  },

  created: async function() {
    await this.findItems({
      query: this.findQuery,
    })
  }

I've also tried setting operator aliases in sequelize.js, although I understand that's not a good idea

const Sequelize = require('sequelize');
const Op = Sequelize.Op;

module.exports = function(app) {
  const connectionString = app.get('postgres_data');
  const sequelize_data = new Sequelize(connectionString, {
    dialect: 'postgres',
    operatorsAliases: {
      $regexp: Op.regexp,
      $iRegexp: Op.iRegexp
    }

Expected behavior

To be able to query with $regexp and $iRegexp

Actual behavior

I get a error

type: "FeathersError",
className: "bad-request"
code: 400
data: Object { "$regexp": "root.*" }
errors: Object {  }
hook: Object { type: "before", method: "find", path: "data", … }

System configuration

Tell us about the applicable parts of your setup.
Postgresql 12

Module versions (especially the part that's not working):
"@feathersjs/feathers": "^4.4.3",
"@feathersjs/socketio-client": "^4.4.3",
"feathers-vuex": "^3.2.0",
"socket.io-client": "^2.3.0",
"vue": "^2.6.11",
"vuex": "^3.1.2",
"vuex-router-sync": "^5.0.0"

NodeJS version:
v13.3.0

[marshallswain]

Is this error coming across the websocket?

[Adam-0-Moore]

Sorry, I'm really new at this, but I believe so. I see the error in the console of the devtools.

[marshallswain]

To see if it's coming across the websocket, you need to open the Network pane and filter down to WS (which is the websocket filter). You'll see your connection in the list. Click on it and you'll see the request messages on the right. Look through the responses to verify if it's coming across the Websocket connection. That will give me a much more direct way of figuring out how to help you.

[Adam-0-Moore]

Yes, it does look like it's coming across the websocket.

This is sent:
421["find","category",{"$sort":{"id":1},"$limit":100,"path":{"$regexp":"root.*"}}]

and this is received:
431[{"name":"BadRequest","message":"Invalid query parameter $regexp","code":400,"className":"bad-request","data":{"$regexp":"root.*"},"errors":{}}]

[marshallswain]

Ok. This might mean that you need to add the custom query params to the whitelist for the service on the client: https://github.com/feathersjs-ecosystem/feathers-sequelize#api

Usually, the "invalid query parameter" means that those params aren't added to the whitelist.

[Adam-0-Moore]

Oh, I'm sorry, I think I'm not following - is this different from adding whitelist: ['$regexp', '$iRegexp', '$options'] to feathers-client.jp

[marshallswain]

It is different. You need to specify the whitelist on the client AND on the API server. Each service on the server has a whitelist option that works exactly the same way as it does on the client. When you whitelist on the server, it will probably work. Here's an example of one of my server-side services. Notice that I have to manually enable $regex for that service.

[Adam-0-Moore]

I missed that! Thanks!

I get a new error - I wonder if I'm writing the query wrong...

431[{"stack":"Error: Invalid value { path: { '$regexp': 'root.*' } }\n at Object.escape (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/sql-string.js:65:11)\n at PostgresQueryGenerator.escape (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:986:22)\n at PostgresQueryGenerator._whereParseSingleValueObject (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2556:41)\n at PostgresQueryGenerator.whereItemQuery (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2274:19)\n at /Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2173:25\n at Array.forEach (<anonymous>)\n at PostgresQueryGenerator.whereItemsQuery (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2171:35)\n at PostgresQueryGenerator.getWhereConditions (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:2583:19)\n at PostgresQueryGenerator.selectQuery (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/dialects/abstract/query-generator.js:1315:28)\n at QueryInterface.rawSelect (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/query-interface.js:1157:37)\n at Function.aggregate (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/model.js:1993:32)\n at /Users/adam/Documents/Projects/test/backend/portal/server/node_modules/sequelize/lib/model.js:2045:19\n at tryCatcher (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/bluebird/js/release/util.js:16:23)\n at Promise._settlePromiseFromHandler (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/bluebird/js/release/promise.js:547:31)\n at Promise._settlePromise (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/bluebird/js/release/promise.js:604:18)\n at Promise._settlePromise0 (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/bluebird/js/release/promise.js:649:10)\n at Promise._settlePromises (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/bluebird/js/release/promise.js:729:18)\n at _drainQueueStep (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/bluebird/js/release/async.js:93:12)\n at _drainQueue (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/bluebird/js/release/async.js:86:9)\n at Async._drainQueues (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/bluebird/js/release/async.js:102:5)\n at Immediate.Async.drainQueues [as _onImmediate] (/Users/adam/Documents/Projects/test/backend/portal/server/node_modules/bluebird/js/release/async.js:15:14)\n at processImmediate (internal/timers.js:445:21)","message":"Invalid value { path: { '$regexp': 'root.*' } }"}]

[marshallswain]

I'm not a Sequelize user, but you might start by reading the Operators security section here: https://sequelize.org/master/manual/querying.html

[Adam-0-Moore]

Ok, Thank you - I'll do that :)

[Adam-0-Moore]

I'm curious, are you not an SQL user, or do you use SQL with something other than Sequelize?

[marshallswain]

I used to do absolutely everything in SQL, since my PHP days 18 years ago. Postgres is really great, now, too. These days I really prefer building apps with MongoDB.

[marshallswain]

If I were starting a new app, though, I would probably use feathers-objection, based on what long-time feathers-sequelize users have said in the Slack channel in the last month or so.

[Adam-0-Moore]

Thank you - Since I'm just starting I'll have a look at that!

[Adam-0-Moore]

I finally figured out Objection, and querying ltree columns with regex just works.

I wanted to write back with a solution for sequelize, but I never could figure it out.

Thanks for recommending feathers-objection :)

[okaracalik]

I hade a similar problem and I got a working solution for it.
The solution steps:

1. Firstly, I imported the Op (Operators) from the sequelize library. _[example-model.js]_
2. Then, I modified the operators and whitelist properties of the model. _[example-model.js]_
3. Lastly, I used the new operator in my hook file. _[example-hook.js]_

I hope, it helps.

example-model.js

const { Players } = require('./players.class');
const createModel = require('../../models/players.model');
const hooks = require('./players.hooks');
const Sequelize = require('sequelize');

module.exports = function (app) {
  const options = {
    Model: createModel(app),
    paginate: app.get('paginate'),
    operators: {
      '$iRegexp': Sequelize.Op.iRegexp
    },
    whitelist: ['$iRegexp']
  };

  app.use('/players', new Players(options, app));

  const service = app.service('players');

  service.hooks(hooks);
};

example-hook.js

const { params, service } = context;
if (params.query['search']) {
  params.sequelize = {
    where: {
      'name': {
        [service.options.operators.$iRegexp]: params.query.search
      }
    },
    raw: false
  };
}

[marshallswain]

Thanks @okaracalik for sharing your clean solution with Sequelize. I'm going to go ahead and close this issue, since there's not really anything more to be done in feathers-vuex. It will always be available to others in the Issue search.