How to correctly create models for recursive joins and the relevant input fields to edit the data for a multi-joined table structure in CakePHP?

Question

I have a typical employees table which has various child tables. One child table, employees_specialties, joins an employee to his various specialties and severities for each specialty. Table structure below. Note I reviewed many other similar issues on Stack Overflow but none quite match. I saw this too How do I write a join query across multiple tables in CakePHP? but it doesn't quite address my issue.

Note we are currently stuck on CakePHP 1.3 but are working to migrate to 2.0 though I need to get this working prior to the migration.

employees:

id int
login varchar(50)
email varchar(100)
...other unrelated to this issue fields

specialties:

id int
name varchar(50)

priorities:

id int
name varchar(50)

employees_specialties:

id int
employee_id int
specialty_id int
priority_id int

So the issue is that I need the ability to edit an employees, their Specialties, and the Specialty's Priority. I cannot seem to figure out how to get my models right so that the Employees model will return employees_specialties joined with specialties and with priorities. Currently I get the below structure when I load Employees model. Note how the priority table isn't being joined on the priority_id? I need it to automatically join the tables for the employee. This is the first issue I'm having:

 [data] => Array
        (
            [Employee] => Array
                (
                    [id] => 1
                    [login] => loginname
                    [email] => mymail@example.com
                )
            [EmployeeSpecialty] => Array
                (
                    [0] => Array
                        (
                            [id] => 46
                            [name] => Name
                            [longname] => Long Name
                            [EmployeesSpecialty] => Array
                                (
                                    [id] => 13955
                                    [employee_id] => 1
                                    [specialty_id] => 46
                                    [priority_id] => 1
                                )
                        )
                )

What do I need to do to get CakePHP to do the 2nd join on priority_id?

The second issue is, once the Employee's Priority table is successfully joined, how do I then create a edit view that allows updating the priority of the Employee's Specialty? I can easily update the Employee's Specialty using below code, but the act of setting the priority I don't see how to accomplish using the CakePHP functionality. It would seem that once the above 1st issue works to join the priority to the EmployeesSpecialty, then I would use 'EmployeeSpecialtyPriority'?

echo $this->Form->input('EmployeeSpecialty', array('multiple' => 'checkbox'));

Alternatively, I'd be happy to setup the table structure differently so we have a employees_specialties_priorities table that joins the employees_specialties table to the priorities table. I actually tried this, but Cake is still giving me the limited structure I show above where the Priorities table isn't joined to the EmployeesSpecialites. I just need some assistance on why this is failing!

score 0 · Accepted Answer · answered Jun 27 '19 at 16:47

I was able to use this to get closer to what was needed and created the join in the find: CakePHP find method with JOIN

The results of that I sent to a view, and used non-CakePHP to create the form fields in the View I needed because I could find no other way to do it, and then more mostly non-CakePHP combined with SQL similar to this MySQL - UPDATE multiple rows with different values in one query to generate the SQL query to do the update on a save and get it all working.

How to correctly create models for recursive joins and the relevant input fields to edit the data for a multi-joined table structure in CakePHP?

1 Answers1