如何在 CakePHP 中跨多个表编写连接查询? [英] How do I write a join query across multiple tables in CakePHP?

问题描述

谁能告诉我，如何从 cakePHP 中的多个表中检索连接结果(使用 cakePHP mvc 架构).例如，我有三个表要加入 (tbl_topics, tbl_items, tbl_votes.它们的关系定义如下:一个主题可以有很多项目，一个项目可以有很多票.现在我想检索一个包含计数的主题列表对每个主题的所有项目的所有投票.为此编写的 SQL 查询如下:

can anyone tell me, how to retrieve joined result from multiple tables in cakePHP ( using cakePHP mvc architecture). For example, I have three tables to join (tbl_topics, tbl_items, tbl_votes. Their relationship is defined as following: a topic can have many items and an item can have many votes. Now I want to retrieve a list of topics with the count of all votes on all items for each topic. The SQL query for this is written below:

SELECT Topic.*, count(Vote.id) voteCount 
FROM 
tbl_topics AS Topic 
LEFT OUTER JOIN tbl_items AS Item 
ON (Topic.id = Item.topic_id)
LEFT OUTER JOIN tbl_votes AS Vote
ON (Item.id = Vote.item_id); 

我的问题是我可以使用 $this-><Model Name>->query 函数轻松完成，但这需要将 sql 代码写入我不需要的控制器中不想.我正在尝试找出任何其他方法来做到这一点(例如 find()).

My problem is I can do it easily using $this-><Model Name>->query function, but this requires sql code to be written in the controller which I don't want. I'm trying to find out any other way to do this (like find()).

推荐答案

$markers = $this->Marker->find('all', array('joins' => array(
    array(
        'table' => 'markers_tags',
        'alias' => 'MarkersTag',
        'type' => 'inner',
        'foreignKey' => false,
        'conditions'=> array('MarkersTag.marker_id = Marker.id')
    ),
    array(
        'table' => 'tags',
        'alias' => 'Tag',
        'type' => 'inner',
        'foreignKey' => false,
        'conditions'=> array(
            'Tag.id = MarkersTag.tag_id',
            'Tag.tag' => explode(' ', $this->params['url']['q'])
        )
    )
))); 

在 nate abele 的文章中提到:链接文本

as referred to in nate abele's article: link text

这篇关于如何在 CakePHP 中跨多个表编写连接查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！