Postgre jsonb operators and placeholders

[taxp]

Hello!

I have a problem with making select query with operator ?| that is used in postgre to check if two jsonb items have common keys. My query, written by hand and simplified for the example, is:

select * 
from table_name
where phones::jsonb ?| ARRAY['71111111111', '71234567890']

I used query builder like this:

$queryFactory = new QueryFactory('pgsql');
$select = $queryFactory->newSelect();

$select->cols(['*'])
->from('table_name')
->where("phones::jsonb ?| ARRAY['71111111111', '71234567890']");

$statement = $select->getStatement();

but I got that (notice how "?" has been replaced. Builder supposes that it is placeholder for parameter):

select * 
from table_name
where phones::jsonb :_1_| ARRAY['71111111111', '71234567890']

So, what can I do to get rid off of this?

[pavarnos]

should this be a bug report on https://github.com/auraphp/Aura.Sql ?

[taxp]

@pavarnos , no, it is about the query builder. Look, I found that every "where" condition leads to AbstractQuery's addWhere method and, in the end, to rebuildCondAndBindValues. So there is used regexp to split query by question sign and that causes the noted problem.

[pmjones]

@taxp Would it be sufficient to add a "rawWhere()" method that take the WHERE clause as-is and does not attempt to bind data data to it at all?

[taxp]

@pmjones, yes, I guess that can solve the issue. Also, it is need to notice that "raw where" methods should implement both andRawWhere and orRawWhere options.

[pavarnos]

since we are allowed BC breaks, I wonder if a cleaner solution might be to split off the parameter substitution... eg remove the ...$bind (for ? parameter substitution) from where() to simplify its interface and move that functionality to whereWithBoundParameters() (or similar).

[pmjones]

@pavarnos -- Not bad! If we're doing BC breaks of that kind, though, we're off plan #108 (which is fine with me at this point). Further, maybe the thing to do is eliminate support for ?-mark placeholders entirely, which might really simplify things. The 2nd arg on where() et al. can become a key-value array that just calls bindValue() right then. E.g.:

$select->where('foo = :foo', ['foo' => 'foo_value']);
// behind the scenes, it calls $select->bindValues(['foo' => 'foo_value'])

@pavarnos @harikt Thoughts?

@taxp Would that approach be satisfactory?

[harikt]

@pmjones I am ok with removing ? . It is not easy to read than :name place holders.

[pavarnos]

+1 for removing ? parameters.

[taxp]

@pmjones, agree with removing ?-params.

[pmjones]

This appears to be addressed by #134 -- please let us know if there are other problems!