Laravel - 同时联合 + 分页？

Laravel - Union + Paginate at the same time?

提问by Lior

Brief:

简短的：

I am trying to union 2 tables recipesand poststhen add ->paginate(5)to the queries.

我正在尝试合并 2 个表recipes，posts然后添加->paginate(5)到查询中。

But for some reason I get this error:

但由于某种原因，我收到此错误：

Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select count(*) as aggregate from posts

基数违规：1222 使用的 SELECT 语句具有不同的列数（SQL: (select count(*) as aggregate from posts

Code:

代码：

$recipes = DB::table("recipes")->select("id", "title", "user_id", "description", "created_at")
                    ->where("user_id", "=", $id);

$items = DB::table("posts")->select("id", "title", "user_id", "content", "created_at")
                ->where("user_id", "=", $id)
                ->union($recipes)
                ->paginate(5)->get();

Am i doing something wrong?

难道我做错了什么？

Without ->paginate(5)the query works fine.

没有->paginate(5)查询工作正常。

采纳答案by Razor

You're right, pagination cause problem. Right now, you can create a view and query the view instead of the actual tables, orcreate your Paginatormanually:

你是对的，分页会导致问题。现在，您可以创建一个视图并查询该视图而不是实际的表，或者Paginator手动创建您的：

$page = Input::get('page', 1);
$paginate = 5;

$recipes = DB::table("recipes")->select("id", "title", "user_id", "description", "created_at")
            ->where("user_id", "=", $id);
$items = DB::table("posts")->select("id", "title", "user_id", "content", "created_at")
            ->where("user_id", "=", $id)
            ->union($recipes)
            ->get();

$slice = array_slice($items->toArray(), $paginate * ($page - 1), $paginate);
$result = Paginator::make($slice, count($items), $paginate);

return View::make('yourView',compact('result'));

回答by jdme

I faced this kind of issue already. I found a thread also not about paginationbut about unions.

我已经遇到过这种问题。我发现一个线程也不是关于pagination而是关于unions。

Please see this link : Sorting UNION queries with Laravel 4.1

请参阅此链接：使用 Laravel 4.1 对 UNION 查询进行排序

@Mohamed Azher has shared a nice trick and it works on my issue.

@Mohamed Azher 分享了一个很好的技巧，它可以解决我的问题。

$query = $query1->union($query2);
$querySql = $query->toSql();
$query = DB::table(DB::raw("($querySql order by foo desc) as a"))->mergeBindings($query);

This creates an sql like below:

这将创建一个如下所示的 sql：

select * from (
  (select a as foo from foo)
  union
  (select b as foo from bar)
) as a order by foo desc;

And you can already utilize Laravel's paginatesame as usual like $query->paginate(5). (but you have to fork it a bit to fit to your problem)

你已经可以paginate像往常一样使用 Laravel 了$query->paginate(5)。（但你必须分叉一下以适应你的问题）

回答by Johnny

Reiterating jdme's answerwith a more elegant method from Illuminate\Database\Query\Builder.

用更优雅的方法重申jdme的答案Illuminate\Database\Query\Builder。

$recipes = DB::table("recipes") ..
$items = DB::table("posts")->union($recipes) ..

$query = DB::query()
    ->fromSub($items, "some_query_name");

// Let's paginate!
$query->paginate(5);

I hope this helps!

我希望这有帮助！

回答by Tamilheartz

order by

订购方式

 $page = Input::get('page', 1);

 $paginate = 5;

 $recipes = DB::table("recipes")->select("id", "title", "user_id", "description", "created_at")
                ->where("user_id", "=", $id);
$items = DB::table("posts")->select("id", "title", "user_id", "content", "created_at")
            ->where("user_id", "=", $id)
            ->union($recipes)
            ->orderBy('created_at','desc')
            ->get();

$slice = array_slice($items, $paginate * ($page - 1), $paginate);
$result = Paginator::make($slice, count($items), $paginate);

return View::make('yourView',compact('result'))->with( 'result', $result );

View page :

查看页面：

   @foreach($result as $data)
  {{ $data->your_column_name;}}
 @endforeach 

  {{$result->links();}}   //for pagination

its help to more peoples.. because nobody cant understand show data in view page union with pagination and orderby .. thank u

它对更多人有帮助..因为没有人无法理解在视图页面联合中显示数据与分页和 orderby ..谢谢你

回答by Marcelo Bruzetti

I had this same problem, and unfortunately I couldn't get the page links with {{ $result->links() }}, but I found another way to write the pagination part and the page links appears

我遇到了同样的问题，不幸的是我无法获得页面链接{{ $result->links() }}，但我找到了另一种编写分页部分的方法，页面链接出现了

Custom data pagination with Laravel 5

使用 Laravel 5 自定义数据分页

//Create a new Laravel collection from the array data
$collection = new Collection($searchResults);

//Define how many items we want to be visible in each page
$perPage = 5;

//Slice the collection to get the items to display in current page
$currentPageSearchResults = $collection->slice($currentPage * $perPage, $perPage)->all();

//Create our paginator and pass it to the view
$paginatedSearchResults= new LengthAwarePaginator($currentPageSearchResults, count($collection), $perPage);

return view('search', ['results' => $paginatedSearchResults]);

回答by Sarl James Sebios

The accepted answer works great for Query Builder.

接受的答案非常适合查询生成器。

But here's my approach for Laravel Eloquent Builder.

但这是我对 Laravel Eloquent Builder 的方法。

Assume that we're referring to same Model

假设我们指的是同一个模型

$q1 = Model::createByMe();       // some condition
$q2 = Model::createByMyFriend(); // another condition

$q2->union($q1);
$querySql = $q2->toSql();

$query = Model::from(DB::raw("($querySql) as a"))->select('a.*')->addBinding($q2->getBindings());

$paginated_data = $query->paginate();

I'm using Laravel 5.6

我正在使用 Laravel 5.6

回答by UdaraWanasinghe

Getting the total count for pagination is the problem here. This is the error I got when used $builder->paginate()

获取分页总数是这里的问题。这是我使用时遇到的错误$builder->paginate()

"SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select count(*) as aggregate from `institute_category_places` where `status` = approved and (`category_id` in (3) or `name` LIKE %dancing class% or `description` LIKE %dancing class% or `address_line1` LIKE %dancing class% or `address_line2` LIKE %dancing class% or `city` LIKE %dancing class% or `province` LIKE %dancing class% or `country` LIKE %dancing class%) and `institute_category_places`.`deleted_at` is null) union (select * from `institute_category_places` where `status` = approved and (`category_id` in (3, 4) or `name` LIKE %dancing% or `description` LIKE %dancing% or `address_line1` LIKE %dancing% or `address_line2` LIKE %dancing% or `city` LIKE %dancing% or `province` LIKE %dancing% or `country` LIKE %dancing% or `name` LIKE %class% or `description` LIKE %class% or `address_line1` LIKE %class% or `address_line2` LIKE %class% or `city` LIKE %class% or `province` LIKE %class% or `country` LIKE %class%) and `institute_category_places`.`deleted_at` is null))"

If you want to paginate without total count you can use

如果你想在没有总数的情况下分页，你可以使用

$builder->limit($per_page)->offset($per_page * ($page - 1))->get();

to get only set of rows in the page.

仅获取页面中的一组行。

Getting all the rows and counting total is memory inefficient. So I used following approach to get total count.

获取所有行并计算总数是内存效率低下的。所以我使用以下方法来获得总数。

    $bindings = $query_builder->getBindings();
    $sql = $query_builder->toSql();
    foreach ($bindings as $binding) {
        $value = is_numeric($binding) ? $binding : "'" . $binding . "'";
        $sql = preg_replace('/\?/', $value, $sql, 1);
    }
    $sql = str_replace('\', '\\', $sql);

    $total = DB::select(DB::raw("select count(*) as total_count from ($sql) as count_table"));

Then we have to paginate the result manually.

然后我们必须手动对结果进行分页。

    $page = Input::get('page', 1);
    $per_page = 15;

    $search_results = $query_builder->limit($per_page)->offset($per_page * ($page - 1))->get();

    $result = new LengthAwarePaginator($search_results, $total[0]->total_count, $per_page, $page, ['path' => $request->url()]);

If you can use raw sql queries, it is much more CPU and memory efficient.

如果您可以使用原始 sql 查询，则 CPU 和内存效率会更高。

回答by Rohallah Hatami

for paginate collection do this:

对于分页集合，请执行以下操作：

add this to boot function in \app\Providers\AppServiceProvider

将此添加到 \app\Providers\AppServiceProvider 中的启动功能

  /**
         * Paginate a standard Laravel Collection.
         *
         * @param int $perPage
         * @param int $total
         * @param int $page
         * @param string $pageName
         * @return array
         */
        Collection::macro('paginate', function($perPage, $total = null, $page = null, $pageName = 'page') {
            $page = $page ?: LengthAwarePaginator::resolveCurrentPage($pageName);
            return new LengthAwarePaginator(
                $this->forPage($page, $perPage),
                $total ?: $this->count(),
                $perPage,
                $page,
                [
                    'path' => LengthAwarePaginator::resolveCurrentPath(),
                    'pageName' => $pageName,
                ]
            );
        });

From hereafter for all collection you can paginate like your code

从此以后，您可以像代码一样对所有集合进行分页

$items = DB::table("posts")->select("id", "title", "user_id", "content", "created_at")
                ->where("user_id", "=", $id)
                ->union($recipes)
                ->paginate(5)

回答by Phil

For those who may still look for the answer, I have tried unionand paginatetogether and got right resultunder laravel 5.7.20. This will be better than merging collections then paginate which will not work on big amount of data.

对于那些可能仍在寻找答案的人，我已经一起尝试union并在 laravel 5.7.20下paginate得到了正确的结果。这将比合并集合然后分页要好，这对大量数据不起作用。

Some demo code (in my case, I will deal with multiple databases with same table name):

一些演示代码（在我的例子中，我将处理具有相同表名的多个数据库）：

$dbs=["db_name1","db_name2"]; 
$query=DB::table("$dbs[0].table_name");
for($i=1;$i<count($log_dbs);$i++){
    $query=DB::table("$dbs[$i].table_name")->union($query);
}
$query=$query->orderBy('id','desc')->paginate(50);

I haven't tried on other higher version of laravel. But at least it could work now!

我还没有尝试过其他更高版本的 Laravel。但至少它现在可以工作了！

More information

更多信息

My previous version of laravel is 5.7.9 which will report the Cardinality violationerror. So the laravel team solved this issue in some version of 5.7.x.

我之前的laravel版本是5.7.9会Cardinality violation报错。所以 Laravel 团队在 5.7.x 的某个版本中解决了这个问题。

回答by Ngoc Nam

I know this answer is too late. But I want to share my problems and my solution.

我知道这个答案为时已晚。但我想分享我的问题和我的解决方案。

My problems:

我的问题：

1. Join with many tables at the same time
2. UNION
3. Paginate (Must use, because I have to use a common theme to show pagination. If I made own custom for pagination, it will not match to current. And in the future, a common theme may be changed.)
4. Big data: view took 4 seconds, page load took 4 seconds => total is 8 seconds. (But if I set condition inside that view, it was least than 1 second for total.)

1. 同时加入多张表
2. 联盟
3. Paginate（必须使用，因为我必须使用一个共同的主题来显示分页。如果我自己自定义分页，它会与当前不匹配。并且将来可能会更改一个共同的主题。）
4. 大数据：查看耗时 4 秒，页面加载耗时 4 秒 => 总共 8 秒。（但如果我在该视图中设置条件，则总共不到 1 秒。）

Query

询问

※This is the sample. MariaDB, about 146,000 records.

※这是样品。MariaDB，大约 146,000 条记录。

SELECT A.a_id
     , A.a_name
     , B.organization_id
     , B.organization_name
  FROM customers A 
    LEFT JOIN organizations B ON (A.organization_id = B.organization_id)

UNION ALL

SELECT A.a_id
     , A.a_name
     , B.organization_id
     , B.organization_name
  FROM employees A 
    LEFT JOIN organizations B ON (A.organization_id = B.organization_id)

Solution

解决方案

Reference from www.tech-corgi.com(やり方２), I updated my PHP code to filter inside my query, and then call paginate normally.

参考 www.tech-corgi.com(やり方２)，我更新了我的 PHP 代码以在我的查询中进行过滤，然后正常调用 paginate。

I must add a condition (filter) before getting large records. In this example is organization_id.

在获取大记录之前，我必须添加一个条件（过滤器）。在这个例子中是organization_id。

$query = "
    SELECT A.a_id
         , A.a_name
         , B.organization_id
         , B.organization_name
      FROM customers A 
        LEFT JOIN organizations B ON (A.organization_id = B.organization_id)
     WHERE 1 = 1
       AND B.organization_id = {ORGANIZATION_ID}

    UNION ALL

    SELECT A.a_id
         , A.a_name
         , B.organization_id
         , B.organization_name
      FROM employees A 
        LEFT JOIN organizations B ON (A.organization_id = B.organization_id)

     WHERE 1 = 1
       AND B.organization_id = {ORGANIZATION_ID}
";

$organization_id = request()->organization_id;
$query = str_replace("{ORGANIZATION_ID}", $organization_id, $query);

But it still cannot be used in paginate(). There is a trick to solve this problem. See below.

但它仍然不能在paginate() 中使用。有一个技巧可以解决这个问题。见下文。

Final code

最终代码

Trick: put query inside (). For example: (SELECT * FROM TABLE_A).

技巧：将查询放入(). 例如：(SELECT * FROM TABLE_A)。

Reason: paginage()will generate and run Count query SELECT count(*) FROM (SELECT * FROM TABLE_A), if we did not put inside brackets, Count query would not be a correct query.

原因：paginage()会生成并运行 Count 查询SELECT count(*) FROM (SELECT * FROM TABLE_A)，如果我们没有放在括号内， Count 查询将不是一个正确的查询。

$query = "
    ( SELECT A.a_id
         , A.a_name
         , B.organization_id
         , B.organization_name
      FROM customers A 
        LEFT JOIN organizations B ON (A.organization_id = B.organization_id)
     WHERE 1 = 1
       AND B.organization_id = {ORGANIZATION_ID}

    UNION ALL

    SELECT A.a_id
         , A.a_name
         , B.organization_id
         , B.organization_name
      FROM employees A 
        LEFT JOIN organizations B ON (A.organization_id = B.organization_id)

     WHERE 1 = 1
       AND B.organization_id = {ORGANIZATION_ID}
    ) AS VIEW_RESULT
";

$organization_id = request()->organization_id;
$query = str_replace("{ORGANIZATION_ID}", $organization_id, $query);

$resultSet = DB::table(DB::raw($query))->paginate(20);

Now I can use it normally:

现在我可以正常使用了：

1. SELECT, JOIN, UNION
2. paginate
3. High performance: Filter data before getting

1. 选择、加入、联合
2. 分页
3. 高性能：获取前过滤数据

Hope it help!!!

希望有帮助！！！