When performing a union query on the database and then adding order to the query an error is returned that triggers the incorrect usage of UNION and ORDER BY.
Following the examples in this tutorial:
https://docs.joomla.org/Using_the_union_methods_in_database_queries#union.2C_unionAll_and_unionDistinct
and using this as an example in my module helper file:
$q2
->select('')
->from('#__contact_details')
->where('catid = 26')
->setLimit(5)
;
$q1
->select('')
->from('#__contact_details')
->where('catid = 4')
->setLimit(10)
;
$query
->select('*')
->from('#__contact_details')
->where('1 = 0')
->union($q1)
->union($q2)
->order('name DESC')
;
$test = $db->setQuery($query)->loadObjectList();
The expected result is that item are correctly sorted based on the order specified.
I receive the error: incorrect usage of UNION and ORDER BY
Joomla 3.4
Fix can be found in the libraries/joomla/database/query.php
If you change the order that the order and union objects are referenced the query from the example above will work:
eg
if ($this->union)
{
$query .= (string) $this->union;
}
if ($this->order)
{
$query .= (string) $this->order;
}
Before submitting a pull request I wanted to see if anyone knows if there are any other repercussions in changing the order here.
Thanks
Perfect thanks - confirmed the issue is fixed with your pull request.
Status | New | ⇒ | Closed |
Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2015-04-16 12:14:32 |
I have applied #6693 PR in latest Joomla Staging site (3.6.0) and still having this issue "When performing a union query on the database and then adding order to the query an error is returned that triggers the incorrect usage of UNION and ORDER BY."
I have found another PR which already fixed this issue. Here is the URL: https://issues.joomla.org/tracker/joomla-cms/4127
@fruppel One suggestion for you, You have to first apply above #4127 PR related changes and then need to apply your UNION ALL related changes.
I can confirm the bug exist in Joomla! 3.6.2 and the proposed fix d578064 works (inversion sequence in query.php line 254): write UNION before ORDER BY. On d578064 (2014) was line 374.
Actually Ive just checked out the latest build to send the pull request but that file has been changed and can't see the best place to apply the fix.