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

Question

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?

Answer 1

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