http://joomlacode.org/gf/project/joomla/tracker/?action=TrackerItemEdit&tracker_item_id=34081&start=0

If you combine ->order(...) with ->union(...) in the old version, you will always get the error: "Incorrect usage of UNION and ORDER BY". So there is definitely a bug.
Additional you "merge" query 2 into query 1, (query 2 is a part/element of query 1), so I think, an order is acceptable.

Please provide details on the error "Incorrect usage of UNION and ORDER BY". The methods allow you to specify these options, it doesn't stop you from making errors. Without details it is not possible to say it is a Joomla bug or UBF.

The query object doesn't allow for ORDER BY on result set. A feature request could be made to enable options to be set for the result set. As mentioned, they should be independent from the options on the first set. Don't like options to be switched to result set upon using union, specially because they are intended for the first set. You can add the ORDER BY to the result set by string manipulation. Without a proper feature to set result set options it is not going to be possible to use those options on the first set.

As requested by @sovainfo several months ago pleas provide further information or this will be closed in a few weeks.

A change like this would definitely need to be accompanied by unit tests in any case.

Oh, sorry, I missed that PR

        $query1 = JFactory::getDbo()->getQuery(true);
        $query1->select(array('id', 'title'))->from('#__content')->where('title LIKE ' . $query1->q($query1->e('A', true) . '%', false));

        $query2 = JFactory::getDbo()->getQuery(true);
        $query2->select(array('id', 'title'))->from('#__content')->where('title LIKE ' . $query1->q($query1->e('B', true) . '%', false));

        $query1->union($query2)->order('id DESC');

        JFactory::getDbo()->setQuery($query1)->execute();

@chrisdavenport the first question is: is this a bug and if yes, should it be fixed...before (I think) it makes not much sense to write tests

I don't know if it's a bug or not. A test (or a collection of tests) would clearly demonstrate the expected vs. actual output and make it easier to decide. Bear in mind that we do not guarantee that it's impossible to generate invalid queries.

After 6 months without update I am closing this at this time. It can always be reopened at a later date if needed

I can confirm that this is bug. You can try any of examples from https://docs.joomla.org/Using_the_union_methods_in_database_queries where are used order and replicate "Incorrect usage of UNION and ORDER BY", at least in mysql.

I am also having the same issue.

I am getting

It should be like

ORDER BY should be in the end.

I can reproduce this issue. There is a bug when we use UNION and ORDER together in query.

System Information:
Joomla! 3.4.7 Stable [ Ember ] 21-December-2015 16:00 GMT
5.4.45-3+donate.sury.org~precise+3
Apache/2.2.22 (Ubuntu)

Set to "open" on behalf of @gunjanpatel by The JTracker Application at issues.joomla.org/joomla-cms/4127

Reopening issue as we are having issues at least in MySQL.

I have tested this item ✅ successfully on d578064

Current patch works. Not sure this is valid fix or not but when I have applied patch it works for me. May be we need more tests in different environments.

I have tested this item ✅ successfully on d578064

Can you provide a clear test information. thanks

@alikon You can use this example query to reproduce this issue.

$db = JFactory::getDbo();

$query1 = $db->getQuery(true);
$query1->select(array('id', 'title'))
        ->from('#__content')
        ->where('title LIKE ' . $query1->q($query1->e('A', true) . '%', false));

$query2 = $db->getQuery(true)
            ->select(array('id', 'title'))
            ->from('#__content')
            ->where('title LIKE ' . $query1->q($query1->e('B', true) . '%', false));

$query1->union($query2);

$query1->order('id DESC');

echo $query1->dump();

This is query is the same which suggested by @bembelimen before. Just formatted it for more readability.

I have tested this item ✅ successfully on d578064

tested @icampus:
1. reproduce problem
Using a sample component-> Model with 2 query Objects , union them, and ordering results in SQL Error
2. Patch
Fixed the Problem

This will not be merged without unit tests as requested by @chrisdavenport

For what it is worth the MySQL documentation states "To use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. "
Source: http://dev.mysql.com/doc/refman/5.7/en/union.html

A query like this

(SELECT id FROM c7lja_content WHERE id=10 AND id=1)
ORDER BY id
UNION
(SELECT id FROM c7lja_content WHERE id=11 AND id=2)

will not work as it throws the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION
(SELECT id FROM c7lja_content WHERE id=11 AND id=2)' at line 3

whereas the query in this format is correct:

(SELECT id FROM c7lja_content WHERE id=10 AND id=1)
UNION
(SELECT id FROM c7lja_content WHERE id=11 AND id=2)
ORDER BY id

In which circumstance does the order belong before the UNION?

I can confirm the bug exist in Joomla! 3.6.2 and the proposed fix works (inversion sequence in query.php line 254): write UNION before ORDER BY. On d578064 (2014) was line 374.

I have tested this item ✅ successfully on d578064

The bug exists in Joomla! 3.6.3 and the proposed fix works

libraries/joomla/database/query.php
254: if ($this->union)
255: {
256: $query .= (string) $this->union;
257: }
258:
259: if ($this->order)
260: {
261: $query .= (string) $this->order;
262: }

Here is a workaround:
$query1 = JFactory::getDbo()->getQuery(true);
$query1->select(array('id', 'title'))->from('#__content')->where('title LIKE ' . $query1->q($query1->e('A', true) . '%', false));

$query2 = JFactory::getDbo()->getQuery(true);
$query2->select(array('id', 'title'))->from('#__content')->where('title LIKE ' . $query1->q($query1->e('B', true) . '%', false));

//$query1->union($query2)->order('id DESC');
//JFactory::getDbo()->setQuery($query1)->execute();

/* append the ORDER BY clause as string */
$query1->union($query2);
JFactory::getDbo()->getQuery(true)->setQuery((string)$query1.' ORDER BY id DESC')->execute();

I just wanted to look at this problem. I have noticed that it is already implemented in the branch Joomla! 3.7.0-dev.

Can we close it as it is fixed at #10817

it doesn't makes sense to order individual queries because UNION merge the results into one result so this is a bug