Cakephp 中的 UNION 语法

2021-12-21 00:00:00 sql union php mysql cakephp

有谁知道在 CakePHP 中进行 UNION 查询的好方法吗?我想避免使用 $this->query();.

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

有两个表 t1、t2:

With two tables t1, t2:

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

相关文章