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.