laravel/framework — pull request #4111 by daftspunk (2014-04-12 00:05:20+00:00)
Title: [Bug] Unable to limit and order unions

Refs #3901

There is no streamlined method to to sort or limit a union query. Eg:

```
$query1->union($query2)->orderBy('foo');
```

This sorts $query1, not the union.

From MySQL Docs.

*... use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows.*

But I digress, there is no method I can see for applying a limit and order by to the *union itself*.

**Desired SQL:**

```(query 1 sql) union (query 2 sql) order by foo```

**Actual SQL:**

```(query 1 sql order by foo) union (query 2 sql)```

**Current solution (which is pretty bad):**

```
$result = DB::select(DB::raw($query1->toSql() . ' ORDER BY foo'), $query1->getBindings());
```

**Proposed solution**:

Syntax has not changed, logic has been improved.

* If `limit()` or `orderBy()` is called *after* a `union()`, it will affect the union query, not the calling query. To constrain the query, it must be done before `union()` is called. Eg:

```
$query1->orderBy('foo')->union($query2)->orderBy('bar');
```

Resulting SQL:

```(query 1 sql order by foo) union (query 2 sql) order by bar```

Note:

To `limit` or `orderBy` the original query, it should come before the `union`.

No documentation change is required, this is an implied expectation.

Cheers