Ordering a UNION

Question

I have two queries that I am unioning and would like to order them. I would like to order them on the second element, the date. My queries look like this:

$query2
    ->select ($db->quoteName(array('c.event','b.date','b.type','d.name')))
    ->from ($db->quoteName('app_mob_animal', 'a'))
    ->join('INNER', $db->quoteName('app_event_animal', 'b') . 'ON (' .$db->quoteName('a.mob'). '=' .$db->quoteName('b.mob').')')    
    ->join('INNER', $db->quoteName('app_events', 'c') . 'ON (' .$db->quoteName('b.event'). '=' .$db->quoteName('c.id').')') 
    ->join('INNER', $db->quoteName('app_mob', 'd') . 'ON (' .$db->quoteName('a.mob'). '=' .$db->quoteName('d.id').')')
    ->where (($db->quoteName('a.animal') . 'LIKE' . $db->quote($anid)), 'AND')
    ->where (($db->quoteName('a.joined') . '<=' . $db->quoteName('b.date')), 'AND') 
    ->where ('('.$db->quoteName('a.left') . '>=' . $db->quoteName('b.date') .' OR '. $db->quoteName('a.left') .' LIKE '. $db->quote($emptyDate).')');

$query
    ->select ($db->quoteName(array('b.event','a.date','a.type','b.id')))
    ->from ($db->quoteName('app_event_animal', 'a'))
    ->join('INNER', $db->quoteName('app_events', 'b') . 'ON (' .$db->quoteName('a.event'). '=' .$db->quoteName('b.id').')')     
    ->where ($db->quoteName('a.animal') . 'LIKE' . $db->quote($anid))
    ->union ($query2);  

From reading the instructions at [https://docs.joomla.org/Using_the_union_methods_in_database_queries], I think I want to put the query after the union statement. So the last two lines of my second query might look like this:

->union ($query2)
->order($db->quote(date) . ' DESC');    

But I don't think I have the syntax right as I keep getting errors like - 1221 - Incorrect usage of UNION and ORDER BY etc.

What would be the correct way to order this union please?

Answer 1

It's currently not possible to apply set functions like ORDER BY, HAVING or GROUP BY to UNION queries, see https://github.com/joomla/joomla-cms/issues/6789.

But you can build your query a little different and it should work. Try something like

$query3 = $db->getQuery(true)
              ->select('a.*')
              ->from('(' . $query->union($query2) . ') a')
              ->order('date DESC');

See this answer on stackoverflow for another example: https://stackoverflow.com/a/3531301/1823160

Complete example with 3 united queries:

$db = JFactory::getDbo();
$query1 = $db->getQuery(true)->select('*')->from('#__menu')->where('id = 2');
$query2 = $db->getQuery(true)->select('*')->from('#__menu')->where('id = 3');
$query3 = $db->getQuery(true)->select('*')->from('#__menu')->where('id = 4');

$query = $db->getQuery(true)
             ->select('a.*')
             ->from('(' . $query1->union($query2)->union($query3) . ') a')
             ->order('id DESC');

//echo $query->dump();
$result = $db->setQuery($query)->loadObjectList();

Answer 2

Just having looked at your order clause. I see a mistake. You've forgotten to wrap the value on quotes. Try changing it to:

->order($db->quote('date') . ' DESC');

If this doesn't work, try removing the . 'DESC', so you end up with

->order($db->quote('date'));

As a last resort, try adding a column name to the result set like so:

->order($db->quote('date'), $db->quoteName('a.event'));

Answer 3

Fruppel's answer is spot on.

There's a slight gotcha that I came across which may help others. If you are aliasing columns from different tables so that the whole union exposes the same name where the original table column name is different between the tables, then you neeed to ensure the alias list is in the same order in both (or all) queries that form the union.