2

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?

  • Does your query work without the order? – Lodder Apr 20 '15 at 21:30
  • Yes it works @Lodder, I am getting the results I would expect – Hannah Smith Apr 20 '15 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. – mickmackusa Feb 27 at 9:21
2

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();
  • 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? – Hannah Smith Apr 21 '15 at 20:04
  • Yes sorry, the query in the FROM clause needs an alias. I updated my answer. – fruppel Apr 21 '15 at 20:23
  • It works! :0) Thanks very much – Hannah Smith Apr 21 '15 at 20:32
0

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'));
  • 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.... – Hannah Smith Apr 20 '15 at 21:58
  • @HannahSmith - What version of Joomla are you using? – Lodder Apr 20 '15 at 22:22
  • Its Joomla! 3.4.1 (Stable) – Hannah Smith Apr 20 '15 at 22:31
0

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, privacy policy and cookie policy

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