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.

Default

Encountered this problem. Try to use ->simplePaginate(n) instead. – Erma

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

Shall

Thanks! how would you add orderBy to the union query? i tried to add it after the ->union but the "order by" ends up within one of the parentheses (version 4.2.8). – Default

The solution is right but for small amounts of data. – Amabel

In order to get a large amout of data, it is a bad solution. Because ->get will get all data, then slice the array. – Bagworm

It is not actually the solution but just virtual pagination to make the users happy. – Innsbruck

can't believe this is accepted answer lol – Lavish

This will not work for large tables – Obeah

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)

Fabiano

great answer, I am build spesific group by (exlude null and zero index) in merged 2 table union, this way working paginate and ordering. – Metatherian

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!

Gerik

Wow Johnny! thank you so much, it works for me fine, after a day of struggling with similar issue)) Does it mean it will work fine if we have a lot of data, right?? – Ween

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

Abominable

I can't hold myself to say... Superb! – Cornela

Useful when I don't want to miss my model as it has appended props. – Arguello

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

Portsalut

Loading all the rows in php and then slicing them is not very efficient, especially for large tables. – Postmark

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!!!

Bagworm

Exactly the best answer, and the only right one. – Viridity

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.

Preparation

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

Cornela

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]);

Gautier

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)

Tagmeme

Answer 11

You need to change the order between the get method and paginate like in the example below:

$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()->paginate(5);

Surber

Answer 12

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.

Touber

Answer 13

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

Scape

Code-only answers do very little to educate SO readers. Your answer may or may not be a correct answer, but it is in the moderation queue after being marked as "low-quality". Please take a moment to improve your answer with an explanation. – Merriweather