CakePHP LEFT在多个表上加入条件 [英] CakePHP LEFT JOIN on multiple tables with condition

问题描述

我想使用这个CakePHP表单做一个LEFT连接。请参见 CakePHP图书 - 加入表格部分 / p>

I want to use this CakePHP form to do a LEFT join. See CakePHP Book - section on Joining Tables

$options['joins'] = array(
    array('table' => 'channels',
        'alias' => 'Channel',
        'type' => 'LEFT',
        'conditions' => array(
            'Channel.id = Item.channel_id',
        )
    )
);
$Item->find('all', $options);

除了我的LEFT JOIN有一个包含条件的依赖表。在MySQL中，连接看起来像这样

EXCEPT that my LEFT JOIN has a dependent table with conditions. In MySQL the join looks like this

LEFT JOIN (
    channels as Channel 
        INNER JOIN regions as Region ON ( Region.id = Channel.region_id and Region.id=1 )
) ON Channel.id = Item.channel_id

我可以使用 $ options ['joins'] 语法在CakePHP 2.0中做同样的事吗？

Can I do the same thing in CakePHP 2.0 using the $options['joins'] syntax?

推荐答案

所以经过一点小小的调整，我发现这是在CakePHP的技巧。根据SQL EXPLAIN，这是比使用子查询强制LEFT连接表上的条件更快的连接

So after a bit of fiddling, I discovered this does the "trick" in CakePHP. According to SQL EXPLAIN, this is a much faster join than using a sub-query to force conditions on the LEFT join table

$options['joins'] = array(
    array('table' => '(channels as `Channel` INNER JOIN regions as `Region`
                      ON ( `Region`.id = `Channel`.region_id and `Region`.id=1 ))',
//        'alias' => 'Channel',  // the alias is 'included' in the 'table' field
        'type' => 'LEFT',
        'conditions' => array(
            'Channel.id = Item.channel_id',
        )
    )
);
$Item->find('all', $options);

这篇关于CakePHP LEFT在多个表上加入条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！