Somnath Muluk Somnath Muluk - 1 month ago 12
PHP Question

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?

Answer

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