使用 Laravel 4.1 对 UNION 查询进行排序

Sorting UNION queries with Laravel 4.1

提问by HymanPoint

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

我认为unionLaravel 4 和 Laravel 4.1 之间有一些变化。我有2个模型。

$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_atfield.

我想合并 2 个查询并使用该created_at字段对 2 个查询进行排序。

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

With Laravel 4 it gives me this query:

使用 Laravel 4，它给了我这个查询：

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:

这工作正常，它将两个查询的结果排序在一起。在 Laravel 4.1 中，它给了我这个查询：

(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:

这会产生一个视频列表，然后是一个有序的照片列表。我需要有一个列表，其中对组合查询进行了排序。我希望 Laravel 给我这个查询：

(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?

如何在 Laravel 中使用它？

采纳答案by HymanPoint

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

在这个拉取请求中似乎已修复：https: //github.com/laravel/framework/pull/3901

回答by Mohamed Azher

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.

导致 order by 子句单独添加到 $query 1。

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

将 orderBy 单独添加到 $query1 和 $query2，然后执行如下联合

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

这显然有效，但它不会产生与对联合结果执行 orderBy 相同的结果。

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.

这就是诀窍。

回答by Barmar

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

我不太了解 Laravel，但我敢打赌这会做到：

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

回答by Joel Hinz

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

如果您在链接中向它们添加 orderBy 方法，它应该可以工作，如下所示：

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

现在，正如 Barmar 所说，Laravel 只知道应该对照片查询进行排序，因为你在第三行中这样做了，如果你像上面那样做，可以将其删除。

回答by Pratik Shah

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

您可以尝试DB::query()如下：

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!

我想据我所知它会起作用。仍在寻找实际解决方案！