0

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] => [email protected] ) [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!

1 Answer 1

0

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.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.