How to create the correct CakePHP request for a nested relationship

Question

I am new to CakePHP and I am trying to create a complex query for results. This is killing me. Maybe someone can help me here with this. I am using cake 2.7

I have two tables with 3 relationships (many to many). Neighbourhood and Polygon . The fact is that this Neighbourhood event has many Neighbourhoods , and it also belongs to many Neighbourhoods . Neighbourhood also has many Polygons and Polygon belongs to many Neighbourhoods .

The Neighbourhood table contains 2 fields name and zip . What I get from the user is the zip code .

And now what I want: I want to get all the Polygons from Neighbourhood and his Neighbours . Where Neighbourhood.zip = defined by user .

How can i do this? Should I write a user request or split the process into smaller steps? I struggle with this all day.

Here's what the relationship models look like:

 class Neighbourhood extends AppModel { var $hasAndBelongsToMany = array( 'Neighbourhoods' => array( 'className' => 'Neighbourhood', 'joinTable' => 'neighbourhoods_neighbours', 'foreignKey' => 'neighbourhood_id', 'associationForeignKey' => 'neighbour_id', 'unique' => false ), 'Polygon' => array( 'className' => 'Polygon', 'joinTable' => 'neighbourhoods_polygons', 'foreignKey' => 'neighbourhood_id', 'associationForeignKey' => 'polygon_id', 'unique' => false ), ); } class Polygon extends AppModel { var $hasAndBelongsToMany = array( 'Neighbours' => array( 'className' => 'Neighbourhood', 'joinTable' => 'neighbourhoods_polygons', 'foreignKey' => 'polygon_id', 'associationForeignKey' => 'neighbourhood_id', 'unique' => false, ) ); } 

Answer 1

You need to enable pent-up behavior in your models or even better install it in your application model.

 public $actsAs = array('Containable'); 

And then start creating your queries, for example.

 $conditions = array( 'conditions' => array('id' => '123'), 'contain' => array( 'Neighbourhood'=>array( 'conditions' => array('Neighbourhood.id' => '123') ) ), // or even joins 'joins' => array( array( 'table' => $this->getTableName('default', 'neighbourhoods'), 'alias' => 'Neighbourhood', 'type' => 'RIGHT', // OR LEFT 'conditions' => array( 'Neighbourhood.id = Polygon.neighbourhood_id', 'Neighbourhood.deleted' => 0, ) ) ) ); $polygons = $this->Polygon->find('all', $conditions); 

If you think this is not enough to fulfill more complex queries, you will need to create your own queries. For example, starting a query from a Polygon model:

  $dbo = $this->getDataSource(); $query = $dbo->buildStatement( array( 'fields' => array( 'Polygon.name AS polygon_name', 'Polygon.size', 'Neighbourhood.name AS neighbourhood_name', 'Neighbourhood.lat', 'IF( Neighbourhood.created > DATE_SUB(NOW(), INTERVAL 1 DAY) , 1 , 0) AS new_neighbourhood' ), 'table' => $dbo->fullTableName($this), 'alias' => 'Polygon', 'limit' => null, 'offset' => null, 'joins' => array( array( 'table' => $this->getTableName('default', 'neighbourhoods'), 'alias' => 'Neighbourhood', 'type' => 'LEFT', 'conditions' => array( 'Neighbourhood.id = Polygon.neighbourhood_id', ), 'order' => 'Neighbourhood.name ASC', ), .... ), 'conditions' => $conditions, 'group' => 'Polygon.name', 'order' => 'Polygon.name ASC', ), $this ); $polygons = $this->query($query); 

And if you think this is not enough, then you should sing an amazing grace like that.

 $polygons = $this->query("Here your sql query"); debug($polygons);