Union Syntax In Cakephp

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

Solution 1:

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:

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

Solution 2:

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 :

classContenuextendsAppModel{
    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.

Solution 3:

Use a view, then select from that:

createview my_union asSELECT*FROM t1
LEFTJOIN t2 ON t1.id = t2.id
LEFTJOIN t3 ON t2.id = t3.id
UNIONSELECT*FROM t1
RIGHTJOIN t2 ON t1.id = t2.id
LEFTJOIN t3 ON t2.id = t3.id
UNIONSELECT*FROM t1
RIGHTJOIN t2 ON t1.id = t2.id
RIGHTJOIN t3 ON t2.id = t3.id

In your code:

select * from my_union

Solution 4:

use a code like this :

$friendsPosts= $this->Posts->find('all')
                ->contain(['Users', 'Languages', 'PostStates'])
                ->innerJoinWith('Users.Dusers', function ($q) {
                    return$q->where(['Dusers.id' => $this->Auth->user('id')]);
                });

        $posts= $this->Posts->find('all')
                ->where(['Posts.post_state_id' => 3])
                ->contain(['Users', 'Languages', 'PostStates']);

    $posts->union($friendsPosts);