Drupal 7 Database Union

By slayerment on 17 Aug 2011 at 19:10 UTC

I am trying to do a mysql union in Drupal 7 and I can't seem to get it right.

Here is what I want:

(
SELECT uid, nid, created
FROM {node}
)
UNION
(
SELECT uid, nid, created
FROM {comment}
)
ORDER BY created DESC

Here is what I have:

$query1 = db_select('node', 'n')
	->fields('n', array('uid', 'nid', 'created'));
$query2 = db_select('comment', 'c')
	->fields('c', array('uid', 'nid', 'created'));
$query1->union($query2, 'UNION ALL');
$result = $query1->orderBy('created', 'DESC')->execute();

The above code is giving me the following error:

PDOException: SQLSTATE[HY000]: General error: 1221 Incorrect usage of UNION and ORDER BY: SELECT n.uid AS uid, n.nid AS nid, n.created AS created FROM {node} n ORDER BY created DESC UNION ALL SELECT c.uid AS uid, c.nid AS nid, c.created AS created FROM {comment} c; Array ( ) in ...

Any help would be much appreciated.

Thanks!
Quinton

Comments

This bug has been reported

Anonymous (not verified) commented 17 August 2011 at 21:59

This bug has been reported (http://drupal.org/node/1145076) but there's no fix for it yet, judging by the comments and issue status it doesn't like it'll be fixed until Drupal 8.

The following works for

netbek commented 13 October 2011 at 11:07

The following works for me:

$query1 = db_select('node', 'n')
    ->fields('n', array('uid', 'nid', 'created'));
$query2 = db_select('comment', 'c')
    ->fields('c', array('uid', 'nid', 'created'))
    ->orderBy('created', 'DESC');
$query1->union($query2, 'UNION ALL');
$result = $query1->execute();

orderBy added to last query object to sort union result. One would do the same to limit the result.

Neither OrderBy nor LIMITs

aangel commented 5 February 2013 at 00:37

Neither OrderBy nor LIMITs are working for me with UNIONs.

The following works for me

reeller_sk commented 21 May 2013 at 08:37

The following works for me either.

Worked !

bbu23 commented 5 December 2013 at 17:24

worked for me :) thanks !!

Thanks

SivaprasadC commented 22 June 2018 at 15:05

This worked for me too...

Should not use "order by" before the union happens

robertdbailey commented 3 February 2015 at 16:42

You can see the query itself if you use the "dpq()" function from the devel module. (This can help you troubleshoot query problems in general.) The solution presented by @Reshma works properly:

http://drupal.stackexchange.com/questions/116391/how-to-add-union-with-p...

Essentially, you create a third query that acts as the union of the first two, and then you add your order-by's and etc. Hope that helps.

Rob Bailey