Question 173777 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel – Error occurs SQLSTATE[21000]: Cardinality violation: 1222

I’m making a request to retrieve data from two tables. The number of columns is the same. But for some reason, an error occurs that says that the select has a different number of columns. And when you make a query for one table, everything is fine.

$asks = DB::table('asks')
->select('id', 'user_id', 'text', 'price', 'ask_id', 'created_at')
->where('user_id', 125)
->union(DB::table('my_requests')
->select('id', 'user_id', 'text', 'price', 'ask_id', 'created_at')
->where('user_id', 125))
->paginate(10);

When I fix on get everything is fine but paginate doesn’t work

Tags:

2

Answers


  1. 0

    Make sure that you are following these 2 conditions:

    First, the number and the orders of columns that appear in all SELECT statements must be the same. This seems to be correct in your code.

    Second, the data types of columns must be the same or compatible.
    Are the data types the same? Check that all things related to the tables are the same.
    You may want to export the database, then check that the structure is the same exactly for both tables and fields.

    Also try the code directly like this to see if it is something in the framework code you are using.

    SELECT id
FROM asks
UNION
SELECT id
FROM my_requests
LIMIT 10;

    This allows you to see if the error is in the database or the code.
    If the above query works directly in phpmyadmin sql query editor or equivalent program.

    Try this as well:
$first = DB::table('asks')
->select('id', 'user_id', 'text', 'price', 'ask_id', 'created_at')
->where('user_id', 125);
 
$asks = DB::table('my_requests')
->select('id', 'user_id', 'text', 'price', 'ask_id', 'created_at')
->where('user_id', 125)
->union($first)
->get();

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

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

return View::make('yourView',compact('result'));
    Login or Signup to reply.
  2. 0

    There is Laravel issue for version <= 5.7, when paginate don’t work with union, see here: https://github.com/laravel/framework/issues/21958

    You need to use custom paginator (load all results and paginate it after, or some library like https://github.com/krossroad/laravel-unionpaginator), or update your dependencies versions

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search