CakePHP 4 JSON column values find or set aliases

Question

So I noticed in the CakePHP 4 documentation their isnt a way to use find on JSON field values in a JSON mySQL column. Every time i try to explain what I'm doing it seems to get lost somewhere in translation so I'll explain what I need to happen:

Find entries based on a JSON column value. So in my table i have a json column named "additional_info", inside "additional_info" there are multiple json field values, for example: {1:{org_id:"",name:"", etc. In order to find what i need, i need to be able to set a condition based on the json field values org_id; if i set a condition with addition_info.org_id = 1, show all the records where 1 is located in the tables json column.

I have been stuck on this for multiple days and haven't found a good way to accomplish this. I have messed with the beforeFind in the model, behaviors, traits and collections. I believe my problem is solved with one of the methods i was messing with, but if not any examples on this would be much appreciated!

If able, i would like this to be set in the table model, as to make the change global.(i am not concerned with this)

I’m modifying a table that was already created and has thousands of records set. That option isn’t exactly feasible atm. — TheSwindler44, Jul 28 '20 at 00:13
I’m hoping there’s a way to set aliases and set that alias to the specific value I’m looking for. — TheSwindler44, Jul 28 '20 at 00:19
MySQL v.5.6.43 - @waspinator that plugin is not supported on cakephp 4 along with the most recent update done not being successful. Is there a more stable alternative plugin? — TheSwindler44, Jul 28 '20 at 16:34
At this point it looks like an example of modifying the SQL statement in beforeFind using JSON_EXTRACT() would be the best way to go about it. Does anyone have an example of this? — TheSwindler44, Jul 28 '20 at 19:52

score 0 · Answer 1 · answered Jul 29 '20 at 13:53

Let's learn how to select field as alias:

https://book.cakephp.org/4/en/orm/query-builder.html#selecting-data

// Results in SELECT id AS pk, title AS aliased_title, body ...
$query = $articles->find();
$query->select(['pk' => 'id', 'aliased_title' => 'title', 'body']);

So in my table i have a json column named "additional_info", inside "additional_info" there are multiple json field values

// $additional_info = {
//    1:{
//        org_id:"",
//        name:""
//    }};

At this point it looks like an example of modifying the SQL statement in beforeFind using JSON_EXTRACT() would be the best way to go about it. Does anyone have an example of this?

// SELECT JSON_UNQUOTE(JSON_EXTRACT(additional_info, $.[1].org_id)) AS org_id

$query->select(['org_id' => 'JSON_UNQUOTE(JSON_EXTRACT(additional_info, $.[1].org_id))'])

Where:

// .. WHERE JSON_UNQUOTE(JSON_EXTRACT(additional_info, $.[1].org_id)) = `1`

$query->where(['JSON_UNQUOTE(JSON_EXTRACT(additional_info, $.[1].org_id))' => '1'])

To reuse your code, you can create custom finder

https://book.cakephp.org/4/en/orm/retrieving-data-and-resultsets.html#custom-finder-methods

in Behavior

https://book.cakephp.org/4/en/orm/behaviors.html#creating-a-behavior

I'm using cake4 and had to use it like `->where([ 'JSON_EXTRACT(payload, "$.is_active") = true']);`, because if would have otherwise used `->where([JSON_EXTRACT(payload, "$.is_active") = true])` it would generate a SQL like `JSON_EXTRACT(payload, "$.is_echtdorsten") '1')`. — Seb, Jun 16 '21 at 06:19

CakePHP 4 JSON column values find or set aliases

1 Answers1