UNION语法在Cakephp [英] UNION syntax in Cakephp

问题描述

任何人都知道在CakePHP中进行UNION查询的好方法？我想避免使用 $ this-> query（）; 。

Anyone knows a good way to make UNION query in CakePHP? I would like to avoid using $this->query();.

：

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

有三个表t1，t2，t3：

With three tables t1, t2, t3:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
LEFT JOIN t3 ON t2.id = t3.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
RIGHT JOIN t3 ON t2.id = t3.id

推荐答案

到框架的功能。别。使用框架提供的。如果没有您所寻求的功能，请执行以下操作：

Too many coders try to limit themselves to the functionality of a framework. DON'T. Use what the framework provides. If it does not have the functionality you seek, then either:

• 将所需的功能编码到类扩展中

或

• 自定义框架中的代码，需要。

通常，开发人员尝试将一个正方形钉入一个圆孔，并绕过太多额外的工作，使代码复杂。回退一步，问你为什么要使用框架开始。它将结构带到非结构化语言。它提供了可靠的可重用基础来构建您的应用程序。它不打算成为一个盒子，使自己和有限。

Often, developers try to hammer a square peg into a round hole and wind up doing way too much extra work that really only makes the code complicated. Take a step back and ask why you are using the framework to begin with. It brings structure to an unstructured language. It provides solid reusable foundation to build your application on. It is not intended to be a box to put yourself in and be limited.

更新：我花了一分钟时间阅读复杂的查找条件，并找到您的答案：

UPDATE: I took a minute to read Complex Find Conditions and found your answer:

$joins = array(
    array(
        'table' => 'test_twos',
        'alias' => 'TestTwo',
        'type' => 'LEFT',
        'conditions' => array(
            'TestTwo.id = TestOne.id',
        )
    ),
    array(
        'table' => 'test_threes',
        'alias' => 'TestThree',
        'type' => 'LEFT',
        'conditions' => array(
        'TestThree.id = TestOne.id',
    )
    )
);

$dbo = $this->getDataSource();
$subQuery = $dbo->buildStatement(
    array(
        'fields' => array('*'),
        'table' => $dbo->fullTableName($this),
        'alias' => 'TestOne',
        'limit' => null,
        'offset' => null,
        'joins' => $joins,
        'conditions' => null,
        'order' => null,
        'group' => null
    ),
    $this->TestOne
);
$query = $subQuery;

$query .= ' UNION ';
$joins = array(
    array(
        'table' => 'test_twos',
        'alias' => 'TestTwo',
        'type' => 'LEFT',
        'conditions' => array(
            'TestTwo.id = TestOne.id',
        )
    ),
    array(
        'table' => 'test_threes',
        'alias' => 'TestThree',
        'type' => 'RIGHT',
        'conditions' => array(
        'TestThree.id = TestOne.id',
        )
    )
);

$dbo = $this->getDataSource();
$subQuery = $dbo->buildStatement(
    array(
    'fields' => array('*'),
    'table' => $dbo->fullTableName($this),
    'alias' => 'TestOne',
    'limit' => null,
    'offset' => null,
    'joins' => $joins,
    'conditions' => null,
    'order' => null,
    'group' => null
    ),
    $this->TestOne
);

$query .= $subQuery;

pr($query);

这篇关于UNION语法在Cakephp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！