bosnadev / database

An extended PostgreSQL driver for Laravel 5 with support for some aditional PostgreSQL data types: hstore, uuid, jsonb.
https://bosnadev.com
MIT License
78 stars 69 forks source link

Add support for wrapping PostgreSQL JSON queries #8

Closed EspadaV8 closed 9 years ago

EspadaV8 commented 9 years ago

I'm currently using https://github.com/darrylkuhn/dialect along side this package to better support the JSON columns in PostgreSQL, however, relationships on an external model to a model that has a JSON column fail because of incorrect wrapping of the JSON operators, e.g.

Undefined column: 7 ERROR:  column jobs.data->>'unit_id' does not exist
select * from "jobs" where "jobs"."data->>'unit_id'" = 2657 and "jobs"."data->>'unit_id'" is not null

The fix for that is in the wrapValue method and simply extends what's already there for the hstore wrapping.

Once that was fixed, there's another error when checking is null and is not null

Undefined function: 7 ERROR:  operator does not exist: jsonb ->> boolean
select * from "jobs" where "jobs"."data"->>'unit_id' = 2657 and "jobs"."data"->>'unit_id' is not null

The fix for this is to wrap the column and key within brackets

select * from "jobs" where "jobs"."data"->>'unit_id' = 2657 and ("jobs"."data"->>'unit_id') is not null
mirzap commented 9 years ago

Thanks.