SQL clause precedence?

[theganyo]

I'm having an issue with precedence of SQL statements. I have something like this:

  Resource
    .query()
    .first()
    .where('id', id)
    .whereJsonSupersetOf('data:permissions', [{ actor: user.id, action: action }])
    .orWhereJsonSupersetOf('data:permissions', [{ actor: user2.id, action: action }])

Which emits SQL like this:

select * from "Resource" where "folderId" = ? and ( "data"#>'{permissions}' )::jsonb @> ?::jsonb or ( "data"#>'{permissions}' )::jsonb @> ?::jsonb

But that's not quite correct... I really need parenthesis around the JSON permission checks. In other words, I'd like to see the ultimate SQL look like this:

select * from "Resource" where "folderId" = ? and ( ( "data"#>'{permissions}' )::jsonb @> ?::jsonb or ( "data"#>'{permissions}' )::jsonb @> ?::jsonb )

Is there a way to force precedence / add parentheses?

Thanks! Scott

[koskimas]

Have you tried this:

  Resource
    .query()
    .first()
    .where('id', id)
    .where(function (builder) {
      builder
        .whereJsonSupersetOf('data:permissions', [{ actor: user.id, action: action }])
        .orWhereJsonSupersetOf('data:permissions', [{ actor: user2.id, action: action }]);
    });

You can add parentheses using functions like this.

[theganyo]

No, I hadn't. It works like a champ! Thanks so much! Was that documented and I just missed it?

[koskimas]

No, not really :D. The documentation is a bit crap at the moment, but I'm writing a new one as we speak. It is documented in knex.js documentation. Objection.js query builder attempts to be 100% compatible with knex query builder, so you can always use knex's docs.

[theganyo]

Ah. I actually did look at Knex docs as well, but I missed that "Grouped Chain" means precedence. :)

Thanks again!