CODEWITHSUNDEEP
mysqllaravel
user1307065
user1307065

Reputation: 147

field constant using laravel query builder

Using laravel/fluent query builder, I'm trying to cause a constant field value to pass through for a union(ed) selection that is subsequently ordered . I haven't found the recipe to do the following with fluent. The unions are easy, but how do you get the field constant to work?

Imagine two simple tables (omitted) and a union select:

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 I manufacture myself. Laravel/fluent does not seem ready to handle this case, given the test cases I've tried. Using RAW for a select works great, until you try to order the pair 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

Upvotes: 15

Views: 14252

Answers (2)

ClockworkCoder
ClockworkCoder

Reputation: 231

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 can 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 may use eager loading with something like:

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

Upvotes: 23

Antonio Carlos Ribeiro
Antonio Carlos Ribeiro

Reputation: 87789

This way, probably:

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

Upvotes: 11

Related Questions