php - 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.

Answer 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;