How to build query which contain foreign keys inside of JSON data (MySQL JSON columns)`

Hello, I have following two tables:

xml_advisors (id:INT, name:VARCHAR, techs:JSON)
xml_techtree (id:INT, name:VARCHAR)

Sample Data:
xml_advisors:

ID	NAME*	TECHS
1	3		{"tech":"Advisor_Aapep_C"}
2	15		{"tech":"Advisor_Aapep_E"}
xml_techtree:
ID	NAME*
1	Advisor_Aapep_C
6	AdvisorUniqueUnitEliteEle_E

As you can see xml_advisor can have many xml_techtree as TECHS column can contain also JSON with array of tech names. How I can build such association in CakePHP 3.5?

Finally I would like to use it in following way:

$advisors = TableRegistry::get('XmlAdvisors')->find()->contain(['XmlTechtreex'])->toArray();

I can easily get results which I need with following raw SQL:

SELECT 
  XmlAdvisors.id AS `XmlAdvisors__id`, 
  XmlAdvisors.item_level AS `XmlAdvisors__item_level`, 
  XmlAdvisors.techs AS `XmlAdvisors__techs`, 
  XmlTechtreex.id AS `XmlTechtreex__id`,
  XmlTechtreex.name AS `XmlTechtreex.name`
FROM 
  xml_advisors XmlAdvisors
LEFT JOIN 
  xml_techtreex as XmlTechtreex 
ON 
  JSON_SEARCH(XmlAdvisors.techs, 'one', XmlTechtreex.name) IS NOT NULL

Could you please help me? How should I create models when associations are done via data inside of JSON string? Is it supported by Cake?

Thank you.

something among the lines:

->leftJoin('XmlTechtreex', $query->newExpr()->isNotNull($query->func()->json_search(["XmlAdvisors.techs, 'one', XmlTechtreex.name" => 'literal'])))

note that json_search is magic function so you wont find it in docs

1 Like

Hi!

Thank you for your response. Unfortunatelly building query in that way will not give me final results in form which comes from contain() - that is the biggest downside. After talk on IRC I came to the conclusion that I have to change my data in database as now building associations will be very hard in Cake.