Enabling RegExp as a valid condition

[nicolasembleton]

Hi,

Background: I'm trying to run queries that will do some very lightweight search, or that will not pain me with case sensitivity problems

In the postgresql.js I can see that the RegExp query parsing seem working but I can't get it to work:

PostgreSQL.prototype.buildExpression =
  function (columnName, operator, columnValue, propertyValue) {
    console.log('Building expression from the PGSQL client');

    if (propertyValue instanceof RegExp) {
      columnValue = "'" + propertyValue.source + "'";
      if (propertyValue.ignoreCase) {
        return new ParameterizedSQL(columnName + ' ~* ?', [columnValue]);
      } else {
        return new ParameterizedSQL(columnName + ' ~ ?', [columnValue]);
      }
    }

It seems the loopback-connector package isn't totally ready for it either and the query doesn't seem to run well in the end.

Is that something normal? Is there a known workaround? (couldn't find it).

[ssh24]

You can use like/nlike/ilike/nilike to query regular expressions. More info on pattern matching in postgres.

Here is a sample code snippet:

model-definition.json

{
  "name": "Post",
  "base": "PersistedModel",
  "idInjection": true,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "title": {
      "type": "string"
    }
  },
  "validations": [],
  "relations": {},
  "acls": [],
  "methods": {}
}

boot-script.js

'use strict';
var util = require('util');
var _ = require('lodash');

module.exports = function(app) {
    var db = app.datasources.postgresqlDs;
    var Post = app.models.Post;

    db.automigrate(function(err) {
        if (err) throw err;
        console.log('\nAutomigrate completed');

        Post.create([{
            title: 'Hello World'
        }, {
            title: 'hello world'
        }, {
            title: 'My Post'
        }, {
            title: 'my post'
        }], function(err, result) {
            if (err) throw err;
            console.log('\nCreated instance: ' + util.inspect(result));

            Post.find({where: {title: {like: 'h%'}}}, function(err, result) {
                console.log('\nFound instance with `like`: ' +  util.inspect(result));

                Post.find({where: {title: {nlike: 'M%'}}}, function(err, result) {
                    console.log('\nFound instance with `like`: ' +  util.inspect(result));
                });
            });
        });
    });
};

output

sakibs-mac:postgresql-90 ssh$ node .
Web server listening at: http://0.0.0.0:3000
Browse your REST API at http://0.0.0.0:3000/explorer

Automigrate completed

Created instance: [ { title: 'Hello World', id: 1 },
  { title: 'hello world', id: 2 },
  { title: 'My Post', id: 3 },
  { title: 'my post', id: 4 } ]

Found instance with `like`: [ { title: 'hello world', id: 2 } ]

Found instance with `like`: [ { title: 'Hello World', id: 1 },
  { title: 'hello world', id: 2 },
  { title: 'my post', id: 4 } ]

Hopefully this answers your question. Closing this issue as resolved. Feel free to reopen if needed.