Laravel - Union + Paginate at the same time?

Question

Brief:

I am trying to union 2 tables recipes and posts then add ->paginate(5) to the queries.

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

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.

Answer 1

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

$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'));

Answer 2

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

Please see this link : Sorting UNION queries with Laravel 4.1

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

$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:

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 paginate same as usual like $query->paginate(5). (but you have to fork it a bit to fit to your problem)

Answer 3

Reiterating jdme's answer with a more elegant method from 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!

Answer 4

The accepted answer works great for Query Builder.

But here's my approach for 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

Answer 5

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

Answer 6

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.)

Query

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

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.

I must add a condition (filter) before getting large records. In this example is 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.

Final code

Trick: put query inside (). For example: (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.

$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

Hope it help!!!

Answer 7

Getting the total count for pagination is the problem here. This is the error I got when used $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.

Answer 8

Using Eloquent

I adapted jdme's answer in order to use it with Eloquent. I created a class extending the default Eloquent Builder and overiding the union method to fix the issue with paginate.

Create app\Builder\BuilderWithFixes.php:

<?php

namespace App\Builder;

use Illuminate\Database\Eloquent\Builder;

class BuilderWithFixes extends Builder
{
    /**
     * Add a union statement to the query.
     *
     * @param  \Illuminate\Database\Query\Builder|\Closure  $query
     * @param  bool  $all
     * @return \Illuminate\Database\Query\Builder|static
     */
    public function union($query, $all = false)
    {
        $query = parent::union($query, $all);
        $querySql = $query->toSql();
        return $this->model->from(\DB::raw("($querySql) as ".$this->model->table))->select($this->model->table.'.*')->addBinding($this->getBindings());
    }
}

In you Model (for example app\Post.php), include the method newEloquentBuilder below to replace the default Eloquent Builder with \App\Builder\BuilderWithFixes:

<?php

namespace App;

use Eloquent as Model;

class Post extends Model
{
    // your model stuffs...

    public function newEloquentBuilder($query)
    {
        return new \App\Builder\BuilderWithFixes($query);
    }
}

Now you can use union + paginate at the same time within your model (in this case Post) normally, like:

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

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

Answer 9

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

Custom data pagination with 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]);

Answer 10

for paginate collection do this:

add this to boot function in \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)

Answer 11

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

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!

More information

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

Answer 12

$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()->toArray();
$slice = array_slice($items, $paginate * ($page - 1), $paginate);
$result = new Paginator($slice , $paginate);