Good morning all of you, it’s 6AM here and very cold
Does anybody know if we can perform a find in a field that is a json array ?
The array contains a list of numbers :
I could not manage to use JSON_CONTAINS or JSON_SEARCH
It is a json array stored in a Mysql table;
I tried a standard find method :
$this->Documents->find()->where([‘Documents.readers JSON_CONTAINS’ => n])
where n can be any number
And I thought CakePHP would just throw that into your query even if it’s invalid:
WHERE Documents.readers = NONEXISTINGFUNC(n)
Which would mean your query should work. That it isn’t tells me either I’m wrong about my understanding of how Cake handles this, or there’s something else going on giving you trouble that is not about CakePHP’s finder capacities.
MySQL definitely has the ability to look inside JSON arrays, but I don’t know how it works. Do you have an example of the actual query that you want Cake to generate, i.e. something that works when you run it directly in the MySQL client (or phpMyAdmin or whatever)? @calzone is right that you can inject arbitrary functions into your SQL with the Cake ORM, but the structure isn’t quite what he’s got there.
You want to execute this exactly as shown, with variables that you’re setting? Or you want to execute it against a column in a table somewhere? If the latter, please show an actual query that you might run on your existing database to get the desired result. It’s easiest for people to help you convert an actual SQL query into an ORM version, rather than guess at what you want to actually do.