How to create a UNION query with ORDER BY and GROUP BY in the Kohana query builder?

Question

I am trying to build a UNION query using the Kohana 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(); 

Instead of adding GROUP BY and ORDER BY at the end of the whole query, it adds it immediately after the second query.

This is the SQL that generates:

  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; 

I want to:

  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; 

Answer 1

The suggestions given here apply in the first request set in the union() method, so just flip it when you put 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(); 

You can also remove the extra ->limit(10) from $names , as it will be ignored and replaced by the symbol $query1 .

Answer 2

This answer since 2011 does not work in Kohana 3.3.

But I found this module: https://github.com/Invision70/kohana-orm-union

Answer 3

You can also extend Kohana_ORM with ORM db_pending:

 class ORM extends Kohana_ORM { public function union($table, $all = TRUE) { // Add pending database call which is executed after query type is determined $this->_db_pending[] = array( 'name' => 'union', 'args' => array($table, $all), ); return $this; } } 

Using:

 ORM::factory('MyModel') ->union(DB::select(DB::expr("'RP' id, 'Pasantías' name, 'Pasantías' short_name, 'R' parent_id, null data"))) ->union(DB::select(DB::expr("'RC' id, 'Capacitación' name, 'Capacitación' short_name, 'R' parent_id, null data"))) ->join(['catalogo', 'p'])->on('catalogo.parent_id', '=', 'p.id') ->where('p.parent_id', 'is', NULL) ->where('catalogo.id', 'not in', ['RV', 'RPA', 'RPT']);