framework: union and pagination creates wrong sql query making it unusable

This issue still exists, just ran into it when trying union’d pagination with GraphQL / Relay.

@gtapps this is how I added your (IMHO perfectly valid) solution in the latest 5.6 branch: I don’t mind creating a new PR for this, but since this is a verbatim copy of your solution, I think you should do this 😉 Credit where credit is due… @taylorotwell @GrahamCampbell can you elaborate why this never made it in? Are we overseeing something here? The various proposed solutions (make a custom Paginator or create a new model linking both models ao.) are just wrong IMHO since unions should work as documented and there’s no reference in the docs that pagination with unions doesn’t work out of the box

--- illuminate/database/Query/Builder.php	2018-05-24 12:08:01.241627889 +0200
+++ illuminate/database/Query/Builder.php	2018-05-24 12:12:33.305623092 +0200
@@ -1988,10 +1988,20 @@
      */
     protected function runPaginationCountQuery($columns = ['*'])
     {
-        return $this->cloneWithout(['columns', 'orders', 'limit', 'offset'])
-                    ->cloneWithoutBindings(['select', 'order'])
-                    ->setAggregate('count', $this->withoutSelectAliases($columns))
-                    ->get()->all();
+
+        if(count($this->unions) < 1) {
+            return $this->cloneWithout(['columns', 'orders', 'limit', 'offset'])
+                        ->cloneWithoutBindings(['select', 'order'])
+                        ->setAggregate('count', $this->withoutSelectAliases($columns))
+                        ->get()->all();
+        } else {
+            return $this->newQuery()
+                        ->selectRaw("COUNT(*) as aggregate FROM (".$this->toSql().") as aggregate_query")
+                        ->mergeBindings($this)
+                        ->get()->all();
+        }
+
+
     }
 
     /**

+1 In my case, I have 2 sale types inside products: BUY NOW or AUCTION.

When trying to do some filtering, I need to merge auction products and buy now products all together, and for this I require the union so I can do some where clauses to the auction products table.

I need to use union all since left join will bring me all products even with the where clauses inside the left join.

What happens is that paginate creates the wrong sql query and doesn’t work with union all, so this needs to be addressed.

Here is my solution which works flawlessly File Illuminate\Database\Query\Builder

public function getCountForPagination($columns = ['*'])
    {
        //Change begins here
        if(count($this->unions) < 1){
            $this->backupFieldsForCount();
            $this->aggregate = ['function' => 'count', 'columns' => $this->clearSelectAliases($columns)];
            $results = $this->get()->all();
            $this->aggregate = null;
            $this->restoreFieldsForCount();
        }else{
            $results = $this->newQuery()
                    ->selectRaw("COUNT(*) as aggregate FROM (".$this->toSql().") as aggregate_query")
                    ->mergeBindings($this)
                    ->get()->all();
        }
        //Change ends here
        if (isset($this->groups)) {
            return count($results);
        }

        if (! isset($results[0])) {
            return 0;
        }

        $item = $results[0];

        if (is_object($item)) {
            return (int) $item->aggregate;
        }

        return (int) array_change_key_case((array) $item)['aggregate'];
    }

@fernandobandeira If we follow the link list oop pattern, then the relationship among tasks can be like:

• App\Task.php

public function nextTask()
{
    return $this->hasOne(App\Task::class, 'next_id');  
}

public function previousTask()
{
    return $this->hasOne(App\Task::class, 'previous_id');  
}

I don’t think the above would create a problem. I still think @ChangePlaces has not defined the relations properly in his models, thats why he is facing this issues. I don’t think its a framework related error.

Why not create a middle table product_categories. In this table have the category_id, and product_id.

Then define the relationship as hasMany. This should solve all your issues. You won’t need to have columns with two category_id and you can scale in the future much easier.

I would recommend readying the relationships of eloquent.

https://laravel.com/docs/5.2/eloquent-relationships