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.

There's no error. It just doesn't seem to apply the orderBy sorting. — Howard, Jan 20 '17 at 05:07
It's not an Eloquent thing, it's a MySQL thing. See the "Followup" in the accepted answer here: http://stackoverflow.com/questions/24683766/how-to-use-order-by-inside-union. Basically: ORDER BY will be disregarded in UNIONs, and may be even dependent on the version of MySQL. — zmippie, Apr 12 '17 at 09:12

score 36 · Answer 1 · edited Jul 11 '19 at 20:49

36

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;

edited Jul 11 '19 at 20:49

Cray

2,774
7
22
32

answered Jul 11 '19 at 19:13

Max Roa

499
4
4

1

But in case both tables have diff numbers of columns it gives error. Is there any way I can use UNION in eloquent with diff columns tables ? – Sunil Pachlangia Dec 20 '19 at 15:29
2

@Sunil For me, that is impossible to union in different number of columns,. unless you will add select allies with null value,. – AbingPj Aug 20 '20 at 09:46
1

Each SELECT statement within UNION must have the same number of columns. The columns must also have similar data types. The columns in each SELECT statement must also be in the same order. – fahad shaikh Feb 18 '21 at 18:45

Jaymin Panchal · Answer 2 · 2017-01-20T07:06:38.050

11

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

edited Jan 20 '17 at 07:06

answered Jan 20 '17 at 05:11

Jaymin Panchal

2,797
2
27
31

2

I don't want it ordered as a whole. I want the individual queries to be ordered within themselves. – Howard Jan 20 '17 at 05:12
can you post the result without union and with union to get know more about the problems – Jaymin Panchal Jan 20 '17 at 05:21
It would be difficult because I simplified the query for the question. Basically no sorting is happening when I orderBy() first and then union. When I do them individually the orderBy() works fine. When I do it based on the answer you posted orderBy() happens as a whole. – Howard Jan 20 '17 at 05:29
I think this may be a Laravel bug. I posted the bug on github. Hopefully it will get resolved soon. – Howard Jan 20 '17 at 05:42
@PrafullaKumarSahu Checking now. – Howard Jan 20 '17 at 05:48
Sec, I'll try that. – Howard Jan 20 '17 at 06:38
It didn't work. I checked what it was bringing back and it had way more info than it should be displaying, like server login and password info. – Howard Jan 20 '17 at 17:47

score 9 · Answer 3 · answered Jan 20 '17 at 07:30

9

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

answered Jan 20 '17 at 07:30

Ng Sek Long

4,233
2
31
38

2

I added ->get(); to the end of your examples $a and $b. The solution you provided sort of works but requires hitting the database for each one which is what I'm trying to avoid because I require multiple unions but this is currently the best answer. – Howard Jan 20 '17 at 23:14
3

It does'n work, when need to paginate merged result with right sort order – Alexey Tsinya Oct 25 '18 at 15:53
I did this to get proper results with datatable result $a->union($b)->get(); [Add get(); after union is applied] – Disha Goyal Oct 28 '22 at 10:16

Laravel Eloquent Union query

3 Answers3

Linked