英文:
Error occurs SQLSTATE[21000]: Cardinality violation: 1222
问题
你的请求用于从两个表中检索数据。列数相同，但出现错误，提示选择的列数不同。查询一个表时一切正常。
$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))
->get(); // 修复get后一切正常，但paginate不起作用
英文:
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
答案1
得分: 1
确保您遵循以下两个条件：
<br />首先，所有SELECT语句中出现的列的数量和顺序必须相同。在您的代码中，这似乎是正确的。
<br />其次，列的数据类型必须相同或兼容。
数据类型相同吗？检查与表相关的所有内容是否相同。
您可以尝试导出数据库，然后检查两个表和字段的结构是否完全相同。
<br />
此外，尝试直接使用以下代码，以查看是否是您正在使用的框架代码中的问题。
SELECT id
FROM asks
UNION
SELECT id
FROM my_requests
LIMIT 10;
这可以让您查看错误是在数据库中还是在代码中。
如果上述查询直接在phpmyadmin sql查询编辑器或等效程序中工作，请尝试以下代码：
$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'));
如果您需要进一步的帮助，请告诉我。
英文:
Make sure that you are following these 2 conditions:
<br />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.
<br />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.
<br />
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.
<br />
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'));
答案2
得分: 0
有关 Laravel 版本 <= 5.7 的问题，当 paginate 与 union 一起使用时不起作用，请参见：https://github.com/laravel/framework/issues/21958
您需要使用自定义分页器（加载所有结果，然后进行分页），或者使用类似 https://github.com/krossroad/laravel-unionpaginator 的库，或者更新您的依赖版本。
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论