0

I have a UNION query, where the second part of the UNION should be ordered separately, not the complete UNION.

The resulting query should look like this:

(
  SELECT id, fname FROM tab1 WHERE fname = 'Frank'
) UNION (
  SELECT id, fname FROM tab2 WHERE lname = 'Sinatra'
  ORDER BY fname DESC
)

My question is: How can I order the second part of the UNION? I know, I could use a db_query, but I'd like to join this db_select in another db_select. And I think I cannot join an executed db_query result and a db_select object.

Here is my current code:

$subquery1 = db_select('tab1','t1');
$subquery1->fields('t1',array('id','fname');
$subquery1->where('fname=:fname',array(':fname'=>'Frank');

$subquery2 = db_select('tab2','t2');
$subquery2->fields('t2',array('id','fname');
$subquery2->where('lname=:lname',array(':lname'=>'Sinatra');
$subquery2->orderBy('fname','DESC');

$subquery1->union($subquery2);

$result = $subquery1->execute();
...

The resulting query is:

(
  SELECT id, fname FROM tab1 WHERE fname = 'Frank'
) UNION (
  SELECT id, fname FROM tab2 WHERE lname = 'Sinatra'
)
ORDER BY fname DESC
2

I found the answer not in Drupal, but in MySQL:

I added a new column to the subqueries with an indicator of the query. The first sub query has the sort column value "0_" and the second one "1_".

Here is the drupal db_select code:

$subquery1 = db_select('tab1','t1');
$subquery1->fields('t1',array('id','fname');
$subquery1->where('fname=:fname',array(':fname'=>'Frank');
$subquery1->addExpression("'0_'",'sort_column');

$subquery2 = db_select('tab2','t2');
$subquery2->fields('t2',array('id','fname');
$subquery2->where('lname=:lname',array(':lname'=>'Sinatra');
$subquery2->addExpression('1_','sort_column');
$subquery2->orderBy('sort_column', 'ASC');
$subquery2->orderBy('fname','DESC');

$subquery1->union($subquery2);

$result = $subquery1->execute();
...
0

I don't know this will help, but you can try:

$subquery1 = db_select('tab1','t1');
$subquery1->fields('t1',array('id','fname');
$subquery1->condition('t1.fname', 'Frank')

$subquery2 = db_select('tab2','t2');
$subquery2->fields('t2',array('id','fname');
$subquery1->condition('t2.lname', 'Sinatra')
          ->orderBy('t2.fname','DESC');

$query = Database::getConnection()
  ->select($subquery1->union($subquery2))
  ->fields(NULL, array('id','fname'))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.