如何在Kohana的查询生成器中使用ORDER BY和GROUP BY建立UNION查询? [英] How do I build a UNION query with ORDER BY and GROUP BY in Kohana's query builder?
问题描述
我正在尝试使用 Kohana的查询生成器构建UNION查询.一切正常,直到我添加了GROUP BY或ORDER BY子句.
I'm trying to build a UNION query using Kohana's query builder. Everything works fine until I add a GROUP BY or ORDER BY clause.
这是我正在使用的代码(简体):
Here is the code I'm using (simplified):
$query1 = DB::select('p.name')
->from(array('person', 'p'))
->where('p.organization', 'LIKE', 'foo%')
->limit(10);
$names = DB::select('sh.name')
->union($query1, FALSE)
->from(array('stakeholder', 'sh'))
->where('sh.organization', 'LIKE', 'foo%')
->group_by('name')
->order_by('name')
->limit(10)
->execute()
->as_array();
它不是在整个查询的末尾添加GROUP BY和ORDER BY,而是在第二个查询后立即添加.
Instead of adding the GROUP BY and ORDER BY at the end of the entire query, it's adding it immediately after the second query.
这是生成的SQL:
SELECT sh.name FROM stakeholder AS sh WHERE sh.organization LIKE 'foo%'
GROUP BY name ORDER BY name LIMIT 10
UNION
SELECT p.name from person AS p WHERE p.organization LIKE 'foo%' LIMIT 10;
我想要的是:
SELECT sh.name FROM stakeholder AS sh WHERE sh.organization LIKE 'foo%'
UNION
SELECT p.name from person AS p WHERE p.organization LIKE 'foo%'
GROUP BY name ORDER BY name LIMIT 10;
推荐答案
此处的子句是从union()
方法中设置的第一个查询中应用的,因此只需将它们放在相反的位置即可:
The clauses here are applied from the first query set up in the union()
method, so just reverse where you're putting them:
$query1 = DB::select('p.name')
->from(array('person', 'p'))
->where('p.organization', 'LIKE', 'foo%')
->group_by('name')
->order_by('name')
->limit(10);
$names = DB::select('sh.name')
->union($query1, FALSE)
->from(array('stakeholder', 'sh'))
->where('sh.organization', 'LIKE', 'foo%')
->execute()
->as_array();
您也可以从$names
中删除该多余的->limit(10)
,因为它将被$query1
中的那个忽略并取代.
You can also remove that superfluous ->limit(10)
from $names
since it will be ignored and superseded by the one in $query1
.
这篇关于如何在Kohana的查询生成器中使用ORDER BY和GROUP BY建立UNION查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!