Sorting UNION queries with Laravel 4.1

Question

I think there is something changed in the union between Laravel 4 and Laravel 4.1. I have 2 models.

$photos = DB::table('photos')->select('id', 'name', 'created_at');
$videos = DB::table('videos')->select('id', 'name', 'created_at');

I want to union the 2 querys and order the 2 querys with the created_at field.

$photos = $photos->orderBy('created_at', 'desc');
$combined = $photos->union($videos);

With Laravel 4 it gives me this query:

select `id`, `name`, `created_at` from `videos`
union
select `id`, `name`, `created_at` from `photos`
order by `created_at` desc

This works ok, it sorts the results for both querys together. In Laravel 4.1 it gives me this query:

(select `id`, `name`, `created_at` from `videos`)
union
(select `id`, `name`, `created_at` from `photos` order by `created_at` desc)

This results in a list of videos and after that an ordered list of photos. I need to have a list where the to combined querys are sorted. I want Laravel to give me this query:

(select `id`, `name`, `created_at` from `videos`)
union
(select `id`, `name`, `created_at` from `photos`)
order by `created_at` desc

How do get this working in Laravel?

Answer 1

This i believe is a bug and is not fixed yet. I have the same issue when trying to sort union queries.

$query1->union($query2)->orderBy('foo','desc') 

causes the order by clause to be added to $query 1 alone.

Adding orderBy individually to $query1 and $query2 and then doing a union like below

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

This obviously works but it does not produce the same result as doing a orderBy on the union's result.

For now, the workaround seem to be doing something like

$query = $query1->union($query2);
$querySql = $query->toSql();
$query = DB::table(DB::raw("($querySql order by foo desc) as a"))->mergeBindings($query);

This would produce a query like:

select * from (
  (select a as foo from foo)
  union
  (select b as foo from bar)
) as a order by foo desc;

And that does the trick.

Answer 2

I don't really know Laravel, but I'll bet this will do it:

$photos = DB::table('photos')->select('id', 'name', 'created_at');
$videos = DB::table('videos')->select('id', 'name', 'created_at');
$combined = $photos->union($videos)->orderBy('created_at', 'desc');

Answer 3

It seems to be fixed in this pull request: https://github.com/laravel/framework/pull/3901

Answer 4

You can use fromSub to create a sub query of unions. I find this much cleaner than using mergeBindings.

$photos = DB::table('photos')->select('id', 'name', 'created_at');
$videos = DB::table('videos')->select('id', 'name', 'created_at')->union($photos);

$result = DB::query()->fromSub($querySql, 'a')->orderBy('year', 'desc')->get();

Answer 5

It should work if you add orderBy methods in the chaining to both of them, like this:

$photos = DB::table('photos')->select('id', 'name', 'created_at')->orderBy('created_at', 'desc');
$videos = DB::table('videos')->select('id', 'name', 'created_at')->orderBy('created_at', 'desc');
$combined = $photos->union($videos);

Right now, as Barmar said, Laravel only knows that the photos query should be ordered, since you do that in your third line, which can be removed if you do it like above.

Answer 6

You can try with DB::query() like below:

DB::query('(Select id,name,created_at from photos) 
           union 
           (Select id,name,created_at from videos) order by created_at ASC');

I guess as of know it will work. Still looking for actual solution!