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.
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;
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();
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
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