How to nest joins with CakePHP?
- by Daren Thomas
I'm trying to behave. So, instead of using following SQL syntax:
select *
from tableA INNER JOIN
tableB on tableA.id = tableB.tableA_id LEFT OUTER JOIN
( tableC INNER JOIN tableD on tableC.tableD_id = tableD.id)
on tableC.tableA_id = tableA.id
I'd like to use the CakePHP model->find(). This will let me use the Paginator too, since that will not work with custom SQL queries as far as I understand (unless you hardcode one single pagination query to the model which seems a little inflexible to me).
What I've tried so far:
/* inside tableA_controller.php, inside an action, e.g. "view" */
$this->paginate['recursive'] = -1; # suppress model associations for now
$this->paginate['joins'] = array(
array(
'table' => 'tableB',
'alias' => 'TableB',
'type' => 'inner',
'conditions' => 'TableB.tableA_id = TableA.id',
),
array(
'table' => 'tableC',
'alias' => 'TableC',
'type' => 'left',
'conditions' => 'TableC.tableA_id = TableA.id',
'joins' = array( # this would be the obvious way to do it, but doesn't work
array(
'table' => 'tableD',
'alias' => 'TableD',
'type' => 'inner',
'conditions' => 'TableC.tableD_id = TableD.id'
)
)
)
)
That is, nesting the joins into the structure. But that doesn't work (CakePHP just ignores the nested 'joins' element which was kind of what I expected, but sad.
I have seen hints in comments on how to do subqueries (in the where clause) using a statement builder. Can a similar trick be used here?
