How to nest joins with CakePHP?

Question

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?

Answer 1

If I'm getting this right, you've got the following relationships (hopefully in your models):

TableA hasMany TableB.
TableA hasMany TableC.

TableB belongsTo TableA.

TableC belongsTo TableA.
TableC belongsTo TableD. (might be hasOne)

TableD hasMany TableC. (might be hasOne)

If you're using the Containable behaviour (I very much recommend it, and set it at app_model level for all models to inherit), I think you can do something like this...

$this->TableA->find(
  'all',
  array(
    'contain' => array(
      'TableB',
      'TableC' => array(
        'TableD'
      )
    ),
    'conditions' => array(...),
    'order' => array(...)
  )
);

If you need to pick specific fields, then you'll need to specify them in the contain parameter, for example here I restrict TableB's returned fields:

$this->TableA->find(
  'all',
  array(
    'contain' => array(
      'TableB' => array(
        'fields' => array(
          'field_1',
          'field_2'
        ),
      ),
      'TableC' => array(
        'TableD'
      )
    ),
    'conditions' => array(...),
    'order' => array(...)
  )
);

The returned data should be like so:

  [0] => array(
    [TableA] => array(
      [id] => 12,
      [name] => 'Foo'
    ),
    [TableB] => array(
      [id] => 23,
      [table_a_id] => 12,
      [name] => 'Bah'
    ),
    [TableC] => array(
      [id] => 45,
      [table_a_id] => 12,
      [table_d_id] => 67,
      [name] => 'Woo',
      [TableD] => array(
        [0] => array(
          [id] => 67,
          [table_a_id] => 12,
          [name] => 'Wah'
        )
      )
    )
  )

However, I've never done this where the nested table is the parent of the container (TableD and TableC), so it might not work, but it's probably worth a try.

Answer 2

It turns out you can't. At least not with the syntax provided above and not with CakePHP 1.2.6. I went over the source (yay! to open source frameworks!) and found the file cake/libs/model/datasources/dbo_source.php which contains the code for the joins.

It all starts with DboSource::renderStatement() which does a shallow walk of the $query['joins'] array, replacing those join definitions with SQL fragments via DboSource::buildJoinStatement($join), which does some tidying up of the arguments (filling in blanks etc.) and then calls DboSource::renderJoinStatement to create the SQL fragment of a single join clause.

me: That should be easy to fix!

I was told not to edit stuff in cake/libs, so instead I copied the file dbo_source.php to app/models/datasources/ for editing. Then I took my axe and refactored the shallow walk of the $query['joins'] array in DboSource::renderStatement() into a new method DboSource::buildJoinStatementArray() resulting in these two methods:

function buildStatement($query, $model) {
    $query = array_merge(array('offset' => null, 'joins' => array()), $query);

    # refactored (extract method) to make recursion easier
    $query['joins'] = $this->buildJoinStatementArray($query['joins']);

    return $this->renderStatement('select', array(
        'conditions' => $this->conditions($query['conditions'], true, true, $model),
        'fields' => implode(', ', $query['fields']),
        'table' => $query['table'],
        'alias' => $this->alias . $this->name($query['alias']),
        'order' => $this->order($query['order']),
        'limit' => $this->limit($query['limit'], $query['offset']),
        'joins' => implode(' ', $query['joins']),
        'group' => $this->group($query['group'])
    ));
}
/**
 * Replaces the join statement array syntax with SQL join clauses.
 */
function buildJoinStatementArray($joins) {
    if (!empty($joins)) {
        $count = count($joins);
        for ($i = 0; $i < $count; $i++) {
            if (is_array($joins[$i])) {
                $joins[$i] = $this->buildJoinStatement($joins[$i]); # $joins[$i] now contains something like "LEFT JOIN users As User on User.group_id = Group.id"
            }
        }
    }
    return $joins;
}

Once I had DboSource::buildJoinStatementArray(), it was time to change DboSource::buildJoinStatement() - all I did was added a check for $data['joins'] and an alternative rendering method for that case:

function buildJoinStatement($join) {
    $data = array_merge(array(
        'type' => null,
        'alias' => null,
        'table' => 'join_table',
        'conditions' => array()
    ), $join);

    if (!empty($data['alias'])) {
        $data['alias'] = $this->alias . $this->name($data['alias']);
    }
    if (!empty($data['conditions'])) {
        $data['conditions'] = trim($this->conditions($data['conditions'], true, false));
    }

    # allow for nested joins
    if (!empty($data['joins']) and is_array($data['joins'])) {
        $data['joins'] = $this->buildJoinStatementArray($data['joins']);
        return $this->renderNestedJoinStatement($data);
    }
    else
    {
        return $this->renderJoinStatement($data);
    }
}

The new renderNestedJoinStatement() method is pretty similar to DboSource::renderJoinStatement():

/**
 * Renders a final SQL JOIN that contains nested join statements
 *
 * @param array $data
 * @return string
 */
function renderNestedJoinStatement($data) {
    extract($data);
    $nestedJoins = implode(' ', $joins);
    return trim("{$type} JOIN ({$table} {$alias} {$nestedJoins})ON ({$conditions})");
}