Laravel Eloquent Union query

Question

So I have the following query:

$a = Model::where('code', '=', $code)
    ->where('col_a', '=' , 1)
    ->orderBy(DB::raw('FIELD(layout, "normal", "split", "flip", "double-faced", "") ASC, layout'))

$b = Model::where('code', '=', $code)
    ->where('col_b', '=' , 1)
    ->orderBy(DB::raw('FIELD(layout, "normal", "split", "flip", "double-faced", "") ASC, layout'))

$a->union($b)->get();

No sorting is happening when I 'orderBy()' first and then union.

When I do query '$a' or '$b' individually the 'orderBy()' works fine.

When I do it in the following way 'orderBy()' happens as a whole.

$a->union($b)
    ->orderBy(DB::raw('FIELD(layout, "normal", "split", "flip", "double-faced", "") ASC, layout'))
    ->get();

How can I make it so the 'orderBy()' applies for each individually and then union the results back? It seems like it should work.

EDIT: If anyone can provide a way to do this, even if it's normal MySQL, I will choose yours as the answer as I think there may be a bug with Eloquent.

Solution 1

Try the following:

$a = Model::where('code', '=', $code)
->where('col_a', '=' , 1);

$b = Model::where('code', '=', $code)->where('col_b', '=' , 1)
->union($a)
->get();

$result = $b;

Solution 2

Just try to apply orderBy() after union()

Try this

$a->union($b)->orderBy(DB::raw('FIELD(layout, "normal", "split", "flip", "double-faced", "") ASC, layout'))->get();

EDIT

Researched about and found and prepared eloquent query just try this

$modelA = Model::where('code', '=', $code)
    ->where('col_a', '=' , 1)
    ->orderBy(DB::raw('FIELD(layout, "normal", "split", "flip", "double-faced", "") ASC, layout'))

$modelB = Model::where('code', '=', $code)
    ->where('col_b', '=' , 1)
    ->orderBy(DB::raw('FIELD(layout, "normal", "split", "flip", "double-faced", "") ASC, layout'))

$a = DB::table(DB::raw("({$modelA->toSql()}) as a"))
    ->mergeBindings($modelA->getQuery())
    ->selectRaw("a.*");

$b = DB::table(DB::raw("({$modelB->toSql()}) as b"))
    ->mergeBindings($modelB->getQuery())
    ->selectRaw("b.*");

$a->union($b)->get();

Solution 3

The "merge" function in Laravel collection might be able to help you.
The big differnt is that I close off the query with a ->get() in advance, and I use merge() instead of union()

$a = Model::where('code', '=', $code)
->where('col_a', '=' , 1)
->orderBy(DB::raw('FIELD(layout, "normal", "split", "flip", "double-faced", "") ASC, layout'))->get();

$b = Model::where('code', '=', $code)
->where('col_b', '=' , 1)
->orderBy(DB::raw('FIELD(layout, "normal", "split", "flip", "double-faced", "") ASC, layout'))->get();

$result = $a->merge($b);

Note: I don't have your data so I can't proof it work, but it least it work on my data so should worth your try