CODEWITHSUNDEEP
cakephpassociations
Benjamin Allison
Benjamin Allison

Reputation: 2154

Filtering with conditions on an associations's association in CakePHP

In my app I have a Burger model, a Joint model, and a Location model.

Joints have many Burgers, and have many Locations.

What I want to do is filter Burgers two ways: based on a rating, and also bases on whether they belong to a Joint that has a Location that matches a condition, in this case, a city.

Here's what I've tried, but she no workey:

$burgers = $this->Burger->find('all', array(
    'conditions' => array('Burger.official_rating !=' => 0),
    'contain' => array(
        'Joint',
            'Joint.Location.conditions' => array(
                'Location.city' => 'Toronto'
            )
    ),
    'order' => array('Burger.official_rating DESC'),
    'limit' => 10
));

Joint.Location.conditions seems to have no effect.

Edit: now using joins, but am unsure if this is the most efficient way (notice, I had to add a Contain clause, to make sure the associated data was returned in the results).

$options['joins'] = array(
    array('table' => 'joints',
        'alias' => 'Joint1',
        'type' => 'inner',
        'conditions' => array(
            'Joint1.id = Burger.joint_id'
        )
    ),
    array('table' => 'locations',
        'alias' => 'Location2',
        'type' => 'inner',
        'conditions' => array(
            'Location2.joint_id = Burger.joint_id'
        )
    )
);

$options['conditions'] = array(
    'Location2.city' => 'Toronto',
    'Burger.official_rating !=' => 0
);

$options['order'] = array(
    'Burger.official_rating DESC',
);

$options['limit'] = 10;

$options['contain'] = array(
    'Joint','Joint.Location'
);

$burgers = $this->Burger->find('all', $options);

Upvotes: 2

Views: 600

Answers (1)

Dave
Dave

Reputation: 29121

Problem:

This is a very common issue people have, but is easily remedied as soon as you understand that you cannot limit the main find model based on conditions against it's contained items.

The reason you can't is because doing a contain actually creates separate queries on the tables, as opposed to JOIN, which creates a single query.

Solution:

Your best bet in this case is to use CakePHP JOINs.

In some cases (not yours), the alternate to using JOINs is to change which model you're doing the query on. If you only need one condition, then you could change the find to the model with the condition instead of the one within the contain and contain in the reverse order.

Upvotes: 2

Related Questions