Select from a table where associated table fieldname = 'x' in Cake PHP

Question

In CakePHP, I have two tables, Countries & Networks. They have a HABTM relationship and are joined by countries_networks.

I'm trying to get all countries from the countries table where the 'name' field in Networks = 'o2'

I've realised I can't do this using a basic find(), so I've been experimenting with the containable behaviour. I have managed to restrict the returned data, but it looks as though 'containable' doesn't exactly work as I want. Heres my code:

$countries = $this->Country->find('all', array('contain' => array(
                                'Network' => array(
                                'conditions' => array('Network.name =' => "o2"),
    )
    )));

This query however returns ALL countries, and the Network.name if its 'o2'. What I really need to do is return ONLY the countries that have a Network.name of 'o2', and no others.

Can anyone help? Thanks.

Answer 1

"=' =>" What is it? there is no needed to use "=" symbol after "Network.name"

Answer 2

You should be able to do something like this:

$this->Country->hasAndBelongsToMany['Network']['conditions'] = array('Network.name'=>'o2');
$myCountries = $this->Country->find('all');

I haven't tested this, but it should get you pretty close to where you need to be.

Also, bear in mind that changing the hasAndBelongsToMany array like this will affect all subsequent queries against that model during the current page load (but it will be reset the next time the model is instantiated).

Answer 3

I'm on my way out the door, so sorry for the brief explanation.

I think what you want is a HABTM relationship, but to be able to filter based on the associated model's data. To do this, check out the "Containable" behavior in Cake's manual. Pretty sure that's what you're after.

Answer 4

Your query returns you exactly what your ask. Try to select from Network.

$countries = $this->Country->Network->find('first', array(
    'conditions' => array('Network.name' => "o2"),
    'contain' => array('Country')
));

$countries = $countries['Country'];