Laravel - Union + Paginate at the same time? and another problem----1222 The used SELECT statements have a different number of columns (SQL: (select count(*) as aggregate from

 

###

这是这几天,碰到的一个比较头疼的问题

使用union all联合查询，同时laravel 生成分页，但发生报错？

QueryException in Connection.php line 729:
SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select count(*) as aggregate from (select * from `orders` where (`status` = completed and `refund_status` in (refunded, not_apply)) or `refund_status` = refunded) as orders left join `users` on `users`.`id` = `orders`.`user_id` left join `order_products` on `order_products`.`order_id` = `orders`.`id` where exists (select 1 from `order_products` where exists (select 1 from `products` where products.id = order_products.product_id) and order_products.order_id = orders.id) and exists (select ....

 

 

###

 

 由于使用联合查询跟使用paginate()来生成分页，导致此问题，解决方法是，先获取到数据，就先不用直接调用分页函数，然后在进行分页处理就可以

 

在stackoverflow ,找到的问题解决方式

https://stackoverflow.com/questions/25338456/laravel-union-paginate-at-the-same-time

 

使用这个方法处理 https://laracasts.com/discuss/channels/general-discussion/paginator-class-is-missing

 

在我决解过程中，会需要引入相关到类

use Illuminate\Support\Facades\Input;
use Illuminate\Pagination\LengthAwarePaginator;

 

       // 处理分页问题
        $page = Input::get('page', 1);
       // Number of items per page
       perPage = 5;
       // Start displaying items from this number;
       $offSet = ($page * $perPage) - $perPage; 
       // Get only the items you need using array_slice (only get 10 items since that's what you need)
       $itemsForCurrentPage = array_slice($report_infos, $offSet, $perPage, true);
        // Return the paginator with only 10 items but with the count of all items and set the it on the correct page
       $report_infos = new LengthAwarePaginator($itemsForCurrentPage, count($report_infos), $perPage, $page, ['path' => request()->url(), 'query' => request()->query()]);

注意⚠️：当我们需要跳转页面当时候，有时我们是还有其他参数当，所以需要传入当前url，跟查询参数
['path' => request()->url(), 'query' => request()->query()]