UNION syntax in Cakephp

Question

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

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

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

Answer 1

Use a view, then select from that:

create view my_union as
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

In your code:

select * from my_union

Answer 2

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

• Code the functionality you need into a class extension

or

• Custom spin the code within the framework to suit your needs.

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 provide 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 (http://book.cakephp.org/view/74/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);