Joins in cakephp using find

Question

I have used join query which joins three tables employee, places and employee_places.The join query gives me all the results as I have used find('all').But in the view page I just want to show employee name from employee table - place name from place table in a dropdown. e.g:- emp1-place1,emp2-place2 and so on. Where should I give the fields name in find. Look at the following code :

$options['joins'] = array( array('table' => 'employees', 'alias' => 'emp', 'type' => 'inner', 'conditions' => array( 'emp.id = EmployeePlace.employee_id ' ) ), array('table' => 'places', 'alias' => 'pl', 'type' => 'inner', 'conditions' => array( 'pl.id = EmployeePlace.place_id' ) ) );

$empTables = $this->Bill->EmployeePlace->find('all', $options);

$this->set(compact('empTables'));

The above query results the following array:-

Array ( [0] => Array ( [EmployeePlace] => Array ( [id] => 1 [employee_id] => 1 [place_id] => 1 [Date] => 2011-02-02 )

        [Employee] => Array
            (
                [id] => 1
                [image] => 
                [firstName] => Andy
                [lastName] => Murray
                [date_of_joining] => 2010-09-02
                [date_of_leaving] => 2011-02-02
                [date_of_birth] => 1991-08-10
                [gender] => Male
                [supervisor] => 0
                [designation] => Manager
                [user_id] => 0
            )

        [Place] => Array
            (
                [id] => 1
                [placeName] => table-1
                [section_id] => 1
                [position] => Left
                [seating_capacity] => 4
            )


    )

)

I just want firstName from employee - placeName from place table in the dropdown. How do I do this using condition in find.

If you give a example on the above code that would be better. — samir chauhan
Virtual fields can only combine fields in one model, if I recall correctly — kaklon

Leo Leo · Accepted Answer · 2011-02-08T14:01:29

Use containable: http://book.cakephp.org/view/1323/Containable

You will then only be dealing with data that you require.

Loop over the results and build the strings you want. Add the strings to an array indexed against the value you want the selection to return. Pass the array out to the view.

How are you going to handle the values? What do you want to do when a user has made a selection?

OR:

You could create a db view, which is a table that combines data from other tables on the fly. CakePHP can access this as a normal table.

Joins in cakephp using find

1 Answers