Laravel Eloquent Union query

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.

This Post Has One Comment

  1. No Fault

    Just try to apply order By() after union()

    Try this

    $a->union($b)->order By(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();

Leave a Reply