How to build proper CakePHP query for nested relations

Question

I am new with CakePHP, and i'm trying to build complicated query for results. It's killing me. Maybe some1 can help me here with that. I'm using cake 2.7

I have two tables with 3 relations (many to many). Neighbourhood and Polygon. Case look like this Neighbourhood has many Neighbourhoods and it also belong to many Neighbourhoods. Also Neighbourhood has many Polygons and Polygon belong to many Neighbourhoods.

Neighbourhood table contains 2 fields name and zip. What i get from user is zip code.

And now what i want: I want to get all Polygons from Neighbourhood and it's Neighbours. Where Neighbourhood.zip = defined by user.

How can i do that? Should i wrote custom query or divide proccess into smaller steps ? I am fighting with this all day.

Here is how models relations look likes:

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 containable behaviour in your models or even better once set it in you app model.

public $actsAs = array('Containable');

And then start to build up you queries e.g.

$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 that this doesn't do enough (in order to do more complicated queries ) then you would need to build up your query statements. e.g. running a query from 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 even this is not enough then you have to sing amazing grace like this..

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

 debug($polygons);