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:

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

    ->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?

  • Does your query work without the order?
    – Lodder
    Apr 20, 2015 at 21:30
  • Yes it works @Lodder, I am getting the results I would expect Apr 20, 2015 at 21:35
  • I feel like your queries might be able to be refactored to avoid a union -- but I would need to see your table schema and expected result to test my assertion. Feb 27, 2019 at 9:21

3 Answers 3


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)
              ->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)
             ->from('(' . $query1->union($query2)->union($query3) . ') a')
             ->order('id DESC');

//echo $query->dump();
$result = $db->setQuery($query)->loadObjectList();
  • Thanks very much @fruppel, its good to know that its an issue. I did try to set the union in the way you suggested but I get a different kind of error then - 0 - JLIB_DATABASE_ERROR_NULL_SUBQUERY_ALIAS. Even if I take the order clause off I still get the error. Any other wise suggestions? Apr 21, 2015 at 20:04
  • Yes sorry, the query in the FROM clause needs an alias. I updated my answer.
    – fruppel
    Apr 21, 2015 at 20:23
  • It works! :0) Thanks very much Apr 21, 2015 at 20:32

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


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

->order($db->quote('date'), $db->quoteName('a.event'));
  • Thanks! But I've just tried all of those and am still getting the 1221 error. It just doesn't seem to like an order query after the union statement.... Apr 20, 2015 at 21:58
  • @HannahSmith - What version of Joomla are you using?
    – Lodder
    Apr 20, 2015 at 22:22
  • Its Joomla! 3.4.1 (Stable) Apr 20, 2015 at 22:31

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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