CakePHP array structure from a HABTM query.

Question

I have two tables linked via a HABTM. countries & networks. They are linked together by the countries_networks lookup table.

I need to get all of the countries and all of the associated network ids for each corresponding country. I don't need to go as far as getting the network names, simply getting the ids from the lookup table will suffice.

If I do a find(all) it gives me an array of countries but not in the structure that I need. I need to return something link this, but I only need Country.countryName and CountriesNetwork.network_id:

Array
(
    [0] => Array
        (
            [Country] => Array
                (
                    [countryName] => Aeroplane
                )
            [Network] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [CountriesNetwork] => Array
                                (
                                    [id] => 1
                                    [country_id] => 1
                                    [network_id] => 1
                                )
                        )

                    [1] => Array
                        (
                            [id] => 7
                            [CountriesNetwork] => Array
                                (
                                    [id] => 2
                                    [country_id] => 1
                                    [network_id] => 7
                                )
                        )
                )
        )
)

Is there a way of doing this with a findall()? as if I pass fields as an array I always seem to get an unknown column names SQL error. Or even a custom query? Many thanks in advance to anyone who may be able to help.

Answer 1

Since you have no model for network_countries it's not possible to get those values using find or findAll. I think the simpliest solution will be to run a custom query to retrieve the data you need.

$query = "SELECT country_id, network_id 
FROM countries C LEFT JOIN network_countries NC ON (NC.country_id = C.id)"
$this->Country->query($query);

Unfortunately the result returned using that code will be formatted slightly different then find results.

Or you could add a model for network countries but I don't think this is a good idea.

Answer 2

Pickledegg,

Sounds like you should investigate the Containable behavior. It will allow you to specify conditions on the joined models like you want, and will return the data formatted in the same manner as the normal find() call.

Check the manual for more information.