Field constant using laravel query builder

Question

Using the laravel / fluq query builder, I am trying to invoke a constant field value to select union (ed), which is subsequently ordered. I did not find a recipe to do the following with fluency. Unions are easy, but how can you make a field work?

Imagine two simple tables (omitted) and select union:

select field1, field2, 'type1' as field3 from table1 UNION select field1, field2, 'type2' as field3 from table2 ORDER BY field2 

The best answer I've come up with so far is to use a DB :: query with a query string that I create myself. Laravel / is free to say that he is not ready to handle this case, given the tests I tried. Using RAW to select works fine until you try to order a couple of selected table queries.

 SELECT field1, field2 FROM ( SELECT fld1A as field1, 'FOO' as field2 from table1 UNION ALL SELECT fld2A as field1, 'BAR' as field2 from table2 ) temp_table order by somefield 

Answer 1

Using Laravel 4 and using GROUP BY rather than ORDER BY I believe you can do something like:

 $t1 = DB::table('table1') ->select('field1',DB::raw("'FOO' as field2")) ->groupBy('field2'); $t2 = DB::table('table2') ->select('field1',DB::raw("'BAR' as field2")) ->groupBy('field2'); $result = $t1->union($t2)->get(); 

I found that $t1 in this case may be an instance of Illuminate\Database\Query\Builder or Illuminate\Database\Eloquent\Builder , but the union argument ( $t2 ) must be of type Illuminate\Database\Query\Builder .

This means that you can use the boot with something like:

$t1 = MyTableModel::with('table3')->select...

Answer 2

Thus, it is possible:

 $users = DB::table('users') ->select(DB::raw("'FOO' as field2")) ->get();