Custom pagination with contains/joins and condition

Question

I am trying to paginate some data from a model called D filtering results based on a specific condition from an indirectly related model. My models look like:

D->C->B->A (where each -> is a belongs to)

I want to paginate on the records of D where A.client = ?

Is this possible using containable? What is the preferred method of doing this (using containable from model D resulted in a query for each paginated item, which seems inefficient)?

Answer 1

Yes, using Containable probably works; e.g.

// function in AController

$this->paginate = array(
    'conditions' => array('A.client' => 'foo'),
    'contain' => array(
        'B' => array(
            'C' => array(
                'D'
            )
        )
    )
);

CakePHP will join A to B, B to C, and C to D. I think it's probably the most straightforward way to get data that is 4 models away. As for inefficiency, you can use the sql_dump element in conjunction with 'explain plan' to make sure that your query uses indexes appropriately.