Union Syntax in Cakephp

UNION syntax in Cakephp

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

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

How can I do sql union in cake php?

You can do this in 4 or more different ways... the easiest but not recomended is using

$this->Model->query($query);

where $query is the query stated above.

The second way but may not be what you want, is to redo your sql query you will get same result (but not separated with the alias) like this:

SELECT * FROM `videos` AS `U1` 
WHERE `U1`.`level_id` = '1' AND (`U1`.`submitted_date` > '2011-09-11' OR `U1`.`submitted_date` < '2011-09-11')
ORDER BY  submitted_date DESC
LIMIT 0,10

This query can be easily done with find like this

$conditions = array(
    'Video.level_id'=>1,
    'OR' => array(
        'Video.submitted_date <'=> '2011-09-11',
        'Video.submitted_date >'=> '2011-09-11'
    )
);
$this->Video->find('all', array('conditions'=>$conditions))

The third way will be the one that Abba Bryant talk about, explained in detail here Union syntax in cakePhp that works building the statement directly.

The fourth way will like the first one more less, you will have to create a behaviour that have a beforeFind function and there you will have to check if a option union and create the query or to create something like the the third option.

you will call it with a find like this

$this->Video->find('all', array('conditions'=>$conditions, 'union'=> $union));

This will be something more less like the linkable or containable behavior.

The fith way is to modified your cakephp sql driver... this one, i don't really know the changes you have to do, but it is a way to get to that... This drivers are the responsible to interpret and create the queries, connect to db and execute the queries...

REMEMBER that cakephp find do the checks neccesary to prevent SQLInyection and other risks... the $model->query will NOT do this tests so be carefull

How do you modify a UNION query in CakePHP 3?

Being able to apply these clauses directly on the query returned by unionAll() is not possible AFAIK, it would require changes to the API that would make the compiler aware where to put the SQL, being it via options, a new type of query object, whatever.

Query::epilog() to the rescue

Luckily it's possible to append SQL to queries using Query::epilog(), being it raw SQL fragments

$unionQuery->epilog('ORDER BY created DESC LIMIT 7 OFFSET 7');

or query expressions

$unionQuery->epilog(
    $connection->newQuery()->order(['created' => 'DESC'])->limit(7)->offset(7)
);

This should give you the desired query.

It should be noted that according to the docs Query::epilog() expects either a string, or a concrete \Cake\Database\ExpressionInterface implementation in the form a \Cake\Database\Expression\QueryExpression instance, not just any ExpressionInterface implementation, so theoretically the latter example is invalid, even though the query compiler works with any ExpressionInterface implementation.

Use a subquery

It's also possible to utilize the union query as a subquery, this would make things easier in the context of using the pagination component, as you wouldn't have to take care of anything other than building and injecting the subquery, since the paginator component would be able to simply apply the order/limit/offset on the main query.

/* @var $connection \Cake\Database\Connection */
$connection = $articles->connection();

$articlesQuery = $connection
    ->newQuery()
    ->select(['*'])
    ->from('articles');
    
$commentsQuery = $connection
    ->newQuery()
    ->select(['*'])
    ->from('comments');
    
$unionQuery = $articlesQuery->unionAll($commentsQuery);

$paginatableQuery = $articles
    ->find()
    ->from([$articles->alias() => $unionQuery]);

This could of course also be moved into a finder.

How to add Null value to match the columns in UNION CAKEPHP 4

Generally you cannot use PHP NULL in the select list, as the value is being used in string concatenation when compiling the SQL, and PHP will convert NULL to an empty string.

You can either use an alias and NULL as a string, eg:

->select(['id', 'first_name', 'last_name' => 'NULL'])

or an expression object that allows you to insert raw SQL directly and omit the alias:

->select(function (\Cake\ORM\Query $query) {
    return [
        'id',
        'first_name',
        $query->newExpr('NULL'),
        // in the first query the alias would be required
        // 'last_name' => $query->newExpr('NULL'),
    ];
})

Note that you can only omit the alias in the query that is being passed to union(), you cannot omit it in the first query, as the ORM needs column names later on when it builds the result set, omitting the alias in the first query would result in an error!

CakePHP 2.2.4: Why does this Union $this-Model-query not work?

My guess is that your original query is invalid SQL. Afaik UNIONS cannot contain multiple 'order by' clauses. As a workaround you may consider to rewrite it to use subqueries like this:

SELECT * FROM (SELECT id, file FROM photos WHERE id <= $id AND page_id = $page_id ORDER BY id DESC LIMIT 3) AS suba
UNION ALL
SELECT * FROM (SELECT id, file FROM photos WHERE id > $id AND page_id = $page_id ORDER BY id ASC LIMIT 2) AS subb

Although I serious think a query like this is far from optimal. Of course, I don't know the way your application works, but it seems that a standard pagination query, with a OFFSET/LIMIT is a more logical approach.

Please take my comment below your question into account; using model->query does NOT automatically handle sanitisation/escaping to prevent SQL injections!

Querying multiple models for similar data in CakePHP (union?)

One option is to create a VIEW which holds the UNION query. And then perform your Cake query on the view.

I'm starting to avoid views recently, however, because of the difficulty in transferring them between servers (and the annoying DEFINER command that is usually included in exports).

Union Syntax in Cakephp