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? — floriank
– floriank, Commented Dec 10, 2014 at 2:10
I will edit the question. Since the duplicate is in older version way 2009, now cake has its ORM. — Ikong
– Ikong, Commented Dec 10, 2014 at 2:16

pedrostanaka · Accepted Answer · 2014-12-11 00:23:27Z

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.

Collectives™ on Stack Overflow

Querying across multiple tables in CakePHP 3.x ORM

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related