Search code examples
phppostgresqlyiiconditional-statementsjsonb

Yii Postgress Json queries for operator with question marks ?, ?|, ?&

How to write SQL queries to support JSON where conditions matching for operators ? , ?|, ?&.

id : 12,
name: 'Harry Pottor',
type: ['Fiction', 'Horror', 'Adventure' ]

In Yii, when I write query for JSON array check with ?& operator gives me error. This query run successfully in pgAdmin

SELECT id, name 
FROM books 
WHERE 
type ?& array['Fiction', 'Horror']

$commandSql = $connection->createCommand($sql);

But ? mark operator gets replaced with $1 i.e. going for bind value.

How to do this type of queries in Yii?

Solution

  • I face same problem 

    SELECT * FROM company WHERE jsonb_exists_all(technology::jsonb, array['ERP']);

    or

    SELECT * FROM company WHERE jsonb_exists_all(technology, array['ERP']);

    refer this link http://www.scriptscoop2.com/t/984beb7bce86/postgresql-json-select-query-replacement-in-php-yii2.html