Union gets appended too soon in database $query

[locii]

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

[locii]

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.

[fruppel]

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

[locii]

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

[RonakParmar]

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.}

[go-source]

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.}