Querying across multiple tables in CakePHP 3.x ORM

Question

I have three tables Countries, Cities and Addresses. Their relationship is Countries hasMany Cities, Cities hasMany Addresses. So Its something like this Countries->Cities->Addresses.

If I want to get all addresses in a certain city I can do it like this

$this->paginate = [
        'contain' => ['Cities']
    ];
    $this->set('addresses', $this->paginate($this->Addresses));

If I want to get all Cities in a certain Country I can do like that as well.

What I want is I want to get all Addresses in a Certain Country.

Note: Addresses does not contain foreign key for Countries, instead its in the Cities.

Possible query would be: SELECT a.*, c.* FROM countries a LEFT JOIN cities b ON a.id=b.country_id LEFT JOIN addresses c ON b.id=c.city_id

possible duplicate of [How do I write a join query across multiple tables in CakePHP?](http://stackoverflow.com/questions/806650/how-do-i-write-a-join-query-across-multiple-tables-in-cakephp) — floriank, Dec 10 '14 at 02:10
I will edit the question. Since the duplicate is in older version way 2009, now cake has its ORM. — Ikong, Dec 10 '14 at 02:16

score 0 · Answer 1 · answered Dec 11 '14 at 00:23

I think you looking for something like this (from the docs):

    <?php

    $query = $countries->find()
        ->hydrate(false)
        ->join([
            'City' => [
                'table' => 'cities',
                'type' => 'LEFT',
                'conditions' => 'Country.id = City.country_id'
            ],
            'Address' => [
                'table' => 'addresses',
                'type' => 'LEFT',
                'conditions' => 'City.id = Address.city_id'
            ]
        ]);

The hydratate method tells the ORM not to try generating entities. If you want the entities to be generated try removing that part.

Querying across multiple tables in CakePHP 3.x ORM

1 Answers1