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?

Solution 1

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

Solution 2

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.

Solution 3

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

Comments

[JackPoint]

This will order the individual querys, not the querys together: (select id, name, created_at from videos order by created_at desc) union (select id, name, created_at from photos order by created_at desc)

[Barmar]

Well, I said I don't know Laravel.

[JackPoint]

This will order the individual querys and put the results in one list, it doesn't order that list.

[Joel Hinz]

My apologies, I misunderstood the question. If it doesn't work with $combined->orderBy('described', 'desc') after that, which I don't think it will, then I'm as lost as you.

[Waiyl Karim]

Worked like a charm. However, it would be helping for newbies to mention that they need to update by composer update and then refer to the link you provided!