Sequelize support for ~ operator with postgres ltree queries

Question

I have a simple postgres ltree query

select * from data_objects WHERE path ~ 'root.*{1}';

I would like to implement this in sequelize, but there is no mapping for the ~ operator.

Is it possible to override the mappings? or do I have to use a raw sql query?

If sequelize does not support database-specific functions, then you might be out of luck. — Tim Biegeleisen, Jul 26 '17 at 11:21
Thanks @GordonLinoff that put me on the right path. I've added answer below. — Click Ahead, Jul 26 '17 at 11:31

score 3 · Answer 1 · answered Jul 26 '17 at 11:33

3

Found the solution:

params.path = {
        $regexp:'root.*{1}'
};

Which translates as

where "path" ~ 'root.*{1}'

I hadn't seen the $regexp operator

answered Jul 26 '17 at 11:33

Click Ahead

2,782
6
35
60

I'm curious: What would `$regexp` do if you were using SQLIte or SQL Server? These databases don't have any native support for regex like. I think this answer still has a caveat that you are running Postgres. – Tim Biegeleisen Jul 26 '17 at 11:57
@TimBiegeleisen $regexp is only supported for MySQL/PG. In any event, the ltree implementation is specific to Postgres. If I change db down the line I'll be looking at a new architecture - so hopefully that doesn't happen :) – Click Ahead Jul 26 '17 at 14:22

score 1 · Answer 2 · answered Jul 26 '17 at 11:26

If you ran your query as a raw query using sequelize, it should work:

sequelize.query("select * from data_objects WHERE path ~ 'root.*{1}'",
                { type: sequelize.QueryTypes.SELECT})
    .then(users => {

    })

Have a look at the documentation for more information. Note that by using a native query you are introducing a hard dependency on Postgres into your Node code. Whether you choose to do this depends on you.

Perhaps you could also rephrase your query using LIKE:

select * from data_objects WHERE LOWER(path) LIKE 'root%{1}'

Thanks, but I was trying to avoid raw queries. I've found regex will do the job. — Click Ahead, Jul 26 '17 at 11:32

Sequelize support for ~ operator with postgres ltree queries

2 Answers2