如何在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'])
        )
    )
)));

：链接文本

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