?
avatar locii
locii
16 Apr 2015

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.

Steps to reproduce the issue

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();

Expected result

The expected result is that item are correctly sorted based on the order specified.

Actual result

I receive the error: incorrect usage of UNION and ORDER BY

System information (as much as possible)

Joomla 3.4

Additional comments

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

avatar locii locii - open - 16 Apr 2015
avatar locii
locii - comment - 16 Apr 2015

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.

avatar fruppel fruppel - reference | - 16 Apr 15
avatar fruppel
fruppel - comment - 16 Apr 2015

I can reproduce this issue. The best palce is probably my open pull request. I made your proposed change to the file in #6693

avatar locii
locii - comment - 16 Apr 2015

Perfect thanks - confirmed the issue is fixed with your pull request.

avatar locii locii - change - 16 Apr 2015
Status New Closed
Closed_Date 0000-00-00 00:00:00 2015-04-16 12:14:32
avatar locii locii - close - 16 Apr 2015
avatar locii locii - close - 16 Apr 2015
avatar fruppel fruppel - reference | 8d6bb9f - 17 Apr 15
avatar RonakParmar
RonakParmar - comment - 13 Jun 2016

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.


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/6789.

avatar pedrobicudo
pedrobicudo - comment - 23 Sep 2016

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.


This comment was created with the J!Tracker Application at issues.joomla.org/joomla-cms/6789.

Add a Comment

Login with GitHub to post a comment