CODEWITHSUNDEEP
phplaravellaravel-4pagination
Lior
Lior

Reputation: 297

Laravel - Union + Paginate at the same time?

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.

Upvotes: 23

Views: 25548

Answers (13)

eliran biton
eliran biton

Reputation: 1

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

Upvotes: 0

cawecoy
cawecoy

Reputation: 2419

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

Upvotes: 1

Phil
Phil

Reputation: 1464

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.

Upvotes: -1

Rohallah Hatami
Rohallah Hatami

Reputation: 569

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)

Upvotes: 0

UdaraWanasinghe
UdaraWanasinghe

Reputation: 2852

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.

Upvotes: 1

ssarljames
ssarljames

Reputation: 131

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

Upvotes: 6

Ngoc Nam
Ngoc Nam

Reputation: 557

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

Upvotes: 1

Johnny
Johnny

Reputation: 316

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!

Upvotes: 7

Razor
Razor

Reputation: 9835

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

Upvotes: 9

tuan cao
tuan cao

Reputation: 1

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

Upvotes: -2

Marcelo Bruzetti
Marcelo Bruzetti

Reputation: 96

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

Upvotes: 0

josevoid
josevoid

Reputation: 677

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)

Upvotes: 9

Murugan Vellaisamy
Murugan Vellaisamy

Reputation: 344

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

Upvotes: 2

Related Questions