php - 如何为嵌套关系构建适当的 CakePHP 查询

Question

我是 CakePHP 的新手，我正在尝试为结果构建复杂的查询。这太痛苦了。也许有人可以帮我解决这个问题。 我用的是cake 2.7

我有两个具有 3 个关系(多对多)的表。 邻域 和多边形。案例看起来像这样 Neighbourhood 有许多 Neighbourhoods 并且它也属于许多 Neighbourhoods。此外，Neighbourhood 有许多 Polygons 并且 Polygon 属于许多 Neighbourhoods。

Neighbourhood 表包含 2 个字段 name 和 zip。我从用户那里得到的是 zip code。

现在我想要的是:我想从 Neighbourhood 获取所有 Polygons，它是 Neighbours。其中 Neighbourhood.zip = 由用户定义。

我该怎么做？我应该编写自定义查询还是将过程分成更小的步骤？我整天都在为此奋斗。

这是模型关系的样子:

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

您需要在您的模型中启用可包含的行为，或者在您的应用模型中设置它后更好。

public $actsAs = array('Containable');

然后开始构建您的查询，例如

$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);

如果您认为这还不够(为了执行更复杂的查询)，那么您需要构建查询语句。例如从多边形模型运行查询:

    $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);

如果你认为这还不够，那么你必须像这样唱奇异恩典..

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

 debug($polygons);