Query Builder making wrong query when using UNION with Pagination

[orszaczky]

Hello,
I tried to make the following paginated UNION query with Query Builder:

$search2 = $search."%";

$query1 = DB::table('fl_foods')
        ->select('id', 'desc')
        ->where('desc', 'LIKE', $search2);

$query = DB::table('fl_foods')
        ->select('id', 'desc')
        ->where('desc', '=', $search)
        ->unionAll($query1)
        ->paginate(10);

For some reason it generates the following aggregate query + error message:

SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: select count(*) as aggregate from fl_foods where desc = ? union all select id, desc from fl_foods where desc LIKE ?) (Bindings: array ( 0 => 'but', 1 => 'but%', ))

This error only happens when I'm trying to paginate the results, it makes a proper UNION query when I use ->limit(10)->get();

Also the same thing happens with SELECT * in both queries.

Is there a way to get this to work, or should I just write a some raw query with custom pagination?

[taylorotwell]

It looks like union and paginate aren't supported right now. I would write the query using the query builder and then use Paginator::make to create a custom Paginator instance.

[ravigehlot]

Hello Taylor,

I ran into an issue where paginator adds a count column causing a cardinality issue where the number of columns differ on an UNION. I wonder if this issue was addressed in 4.1 or even 4.2. I read the release notes. I didn't find anything on that. Like you mentioned, I used the query builder to retrieve the rows, then I sliced it with array_slice() in PHP to build the paginator. It works. The problem is that DB::select() retrieves all rows before it builds the paginator opposed to setting the offset and limit on the query. This causes major delays. There are solutions to this issue. One would be to change the database by combining both tables. That way I wouldn't need to use UNION. It would be bad to change the database at this point because that would brake the app wherever those tables were used (which is in many places). The other solution would be to build 2 queries and then combine then to call paginator on. What do you recommend?

Best,
Ravi.

[chareice]

@ravigehlot Hi, Did you solved this problem which load all rows from database for pagination?

[Mechazawa]

Can this issue be re-opened? It's still not possible to use the paginator without workarounds.

[staudenmeir]

This has been fixed in Laravel 5.7.13 (#26365).