The following SQL query works fine:
mysql> SELECT `id`, `created`, `type`, `saved`->>"$.total" AS `total` FROM `invoices` LIMIT 20;
The saved field is in JSON datatype.
However, when I try to use activerecord query for Gridview widget’s search model:
$query = Invoices::find()->select(['id','created','type','`saved`->>"$.total" AS total']);
the generated SQL statement is going to be faulty due to adding backticks surrounding the JSON key total
SELECT `id`, `created`, `type`, `saved`->>"$.`total"` AS `total` FROM `invoices` LIMIT 20
I have tried several formats for saved fields like:
$query = Invoices::find()->select(['id','created','type','saved->>"$.total" AS total']);
//
mysql> SELECT `id`, `created`, `type`, `saved->>"$`.`total"` AS `total` FROM `invoices` LIMIT 20
// Unknown column error
$query = Invoices::find()->select(['id','created','type','`saved`->>`"$.total`" AS total']);
mysql> SELECT `id`, `created`, `type`, `saved`->>`"$.total`" AS `total` FROM `invoices` LIMIT 20
//Syntax error or access violation
I could not able to manage how to let activerecord query to deliver correct SQL statement?!