如何在CakePHP中的多个表中编写连接查询？

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:

任何人都可以告诉我,如何从cakePHP中的多个表中检索连接结果(使用cakePHP mvc架构)。例如,我有三个表要加入(tbl_topics,tbl_items,tbl_votes。他们的关系定义如下:一个主题可以有很多项目,一个项目可以有很多选票。现在我想检索一个主题列表,其中包括每个主题的所有项目的所有投票。对此的SQL查询如下:

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

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()).

我的问题是我可以使用$ this - > - >查询函数轻松完成,但这需要在我不想要的控制器中编写sql代码。我试图找出任何其他方法来做到这一点(如find())。

5 个解决方案

#1

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

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

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

#2

I'll be honest here and say that you'll probably be a lot happier if you just create a function in your model, something like getTopicVotes() and calling query() there. Every other solution I can think of will only make it more complicated and therefore uglier.

我会诚实地说,如果你只是在你的模型中创建一个函数,比如getTopicVotes()和在那里调用query(),你可能会更开心。我能想到的每一个其他解决方案只会让它更复杂,因此更加丑陋。

Edit:

Depending on the size of your data, and assuming you've set up your model relations properly (Topic hasMany Items hasMany Votes), you could do a simple find('all') containing all the items and votes, and then do something like this:

根据您的数据大小,并假设您已正确设置模型关系(主题有多个项目有多个投票),您可以执行包含所有项目和投票的简单查找(“全部”),然后执行类似的操作这个:

foreach ($this->data as &$topic)
{
    $votes = Set::extract('/Topic/Item/Vote', $topic);
    $topic['Topic']['vote_count'] = count($votes);
}

Two things are important here:

这里有两件事很重要:

If you have a lot of data, you should probably forget about this approach, it will be slow as hell.

如果你有很多数据,你可能应该忘记这种方法,它会很慢。

I've written this from my memory and it might not look like this in real life and/or it may not work at all :-)

我是从记忆中写下来的,在现实生活中可能看起来不像这样,并且/或者它根本不起作用:-)

#3

You can easily set the "recursive" property on a find() query.

您可以在find()查询上轻松设置“递归”属性。

$result = $this->Topic->find('all', array('recursive' => 2));

Alternatively, you can use the Containable behavior in your model. Then you can use:

或者,您可以在模型中使用Containable行为。然后你可以使用:

$this->Topic->contain(array(
    'Item',
    'Item.Vote',
));

$result = $this->Topic->find('all');

$result = $this->Topic->find('all', array(
    'contain' => array(
        'Item',
        'Item.Vote',
    ),
));

#4

What you need is recursive associations support, which is not possible with stock CakePHP currently.
Although it could be achieved using some bindModel trickery
or an experimental RecursiveAssociationBehavior.

你需要的是递归关联支持,目前CakePHP的库存是不可能的。虽然可以使用一些bindModel技巧或实验性的RecursiveAssociationBehavior来实现。

Both of these solutions will either require you to use extra code or rely on a behaviour in your application but if you resist the temptation to write pure SQL code, you'll be rewarded with being able to use Cake`s pagination, auto conditions, model magic etc..

这两种解决方案都要求您使用额外的代码或依赖于应用程序中的行为,但如果您抵制编写纯SQL代码的诱惑,您将获得能够使用Cake的分页,自动条件,模特魔术等..

#5

You should study HaBTM (Has and Belongs to Many) http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

你应该学习HaBTM(Has和Belongs to Many)http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html

#1

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