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
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...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With