CakePHP 2.x containable behavior conditions on deep associations

Question

I have model relations like this:

Project hasMany SubProject hasMany Item

I want to set up a containable array so that I can find all of the Items which belong to a particular Project, and paginate the results. So, in my ItemsController I have:

public $paginate = array(
  'Item' => array(
    'limit' => 10,
    'order' => array('
      'Item.create_time' => 'desc'
    ),
    'contain' => array(
      'SubProject' => array(
        'Project'
      )
    )
  )
);

Somewhere, obviously, I need to place a condition like "SubProject.project_id = $pid", but nothing I've tried yields the correct results. The best I can manage is results that look like this:

Array
(
[0] => Array
    (
        [Item] => Array
            (
                [id] => 13
                [file_name] => foo.tar.gz
                .... other keys ...
                [create_time] => 2013-01-23 14:59:49
                [subProject_id] => 4
            )

        [SubProject] => Array
            (
                [id] => 4
                [name] => foo
                [project_id] => 2
                ..... other keys ....
                [Project] => Array
                    (
                    )

            )

    )
 [1] => Array
 .....

Edit: It is quite correctly omitting the Project record that doesn't match; I want to skip any Item records with out a matching Project record.

It has crossed my mind to manually specify my joins, but I feel like that shouldn't be necessary.

It seems like this should be obvious, but alas, the solution escapes me.

Answer 1

I did eventually solve this problem, so I thought I'd explain what I did in the hope it might help someone else.

After reading this blog post by Mark Story (which is from the days of 1.2 but still relevant) I decided that the thing to do was create a custom find type in my Item model that binds the Project model directly. This gives a first-level association that Containable can filter correctly.

So, in the Items model, I have something like the following (see the documentation on custom find types).

public $findMethods = array('byProject' => true);

public function _findByProject($state, $query, $results=array()) {
    if ($state == 'before') {
        $this->bindModel(array(
            'hasOne' => array(
                'Project' => array(
                    'foreignKey' => false,
                    'conditions' => array('Project.id = SubProject.project_id')
                )
            )
        ));
        return $query;
    }
    return $results;
}

Note that setting foreignKey to false is necessary to prevent CakePHP from trying to automatically use a non-existent database key. In the ItemsController, the pagination options now look like this:

public $paginate = array(
    'Item' => array(
        'findType' => 'byProject',
        'limit' => 10,
        'order' => array(
            'Item.create_time' => 'desc'
        ),
        'contain' => array(
            'SubProject',
            'Project'
        ),
        'conditions' => array('Project.id' = $pid)
    ),
);

...where $pid is the id of the project to display. Some minor tweaks in the view code to accomodate the slightly different results array structure, and I was all set.