Return to Question

added 173 characters in body

Source Link

edited Dec 16, 2013 at 12:22

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

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?

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

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
)

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

Source Link

asked Dec 16, 2013 at 12:16

schulle877

How to order unions separately in db_select?

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?

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

database