UNION syntax in Cakephp

user_316041 0
Viewed 17233

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
cakephp union mysql sql php
4 Answers

A simple way to do this, which we currently use, is to create a view in MySQL or whatever database you use. Then, instead of using a table in your model, you use your view. You can read about view creation syntax here: http://dev.mysql.com/doc/refman/5.6/en/create-view.html. You could also use software like HeidiSQL to help you with view creation.

You would then have something like this in your model : 

class Contenu extends AppModel {
    public $useTable = 'v_contenu';

This allows you to still use the find() method in CakePHP, which is really nice to have.

To get the best performance with views you should update MySQL to at least version 5.6.

user_6430390
Related