In cakephp, how can I set conditions for a model that is not associated with the one I am paginating but which is associated to one that is?

Question

I am paginating ($this->Customer->paginate()) on the 'Customer' model.

The customer model is associated to the 'Contact' model which in turn is associated to the 'ContactAddress' model.

So:

Customer hasMany Contact

Contact belongsTo ContactAddress

Now I want to paginate customers in the 'Customers->index()' using a search query let's say 'ContactAddress.city' LIKE '%New%'

How do I do this? When I do it in the conditions for paginate it says: "Unknown column 'ContactAddress.city' in 'where clause'" logically.

Answer 1

Paginating on a condition twice removed is difficult in any case, Cake or not. You either need to do it in steps (find ContactAddresses and associated Contact.customer_ids first, then paginate on Customer.id IN ($customer_ids)) or you need to do it with subqueries. See the manual on how to do subqueries properly (it ain't pretty).

What's better depends on your coding skills, database size and complexity of the resulting query. Test both and decide what works for you.

Answer 2

I have found a solution that is very very satisfactory.

I looked through the complicated code of the pagination functionality and found, logically, that paginate sets conditions and passes these to a find function of the model (unless the model has it's own 'paginate' function).

I first tried overriding the paginate function, but this was too complicated. The solution I found in the end is to pass on joins to the paginate's options just like you would pass them when doing 'find' on a model:

    //Set the pagination options:   
    `$this->paginate = array(
        'limit' => 25,
        'order' => array(
        'Customer.lastname1' => 'asc'
        ),
        'joins' =>
        array(
                // OUTER JOIN because I wanted to also 
                // fetch record that do not have a 'contact'
        array(
            'table' => 'contacts',
            'alias' => 'Contact',
            'type' => 'LEFT OUTER',
            'conditions' => array(
                'Customer.id = Contact.customer_id',
                'Contact.class' => 'ContactAddress'
            )
            ),
        array(
            'table' => 'contact_addresses',
            'alias' => 'ContactAddress',
            'type' => 'LEFT OUTER',
            'conditions' => array(
                'Contact.index = ContactAddress.id',
            )
            ),
        ),
        // In your conditions you can now use any table that 
        // was joined as well as the original 'customer' table.
        'conditions' => $conditions,

    );

    $this->set('customers',$this->paginate('Customer'));

Either way I hope this helps someone!