Laravel Union查询分页报错：SELECT语句列数不一致问题求助

错误原因 #

Union/UnionAll 要求参与合并的两个查询必须返回完全相同数量的列，且对应位置的列数据类型需兼容。你的代码中：

• $config 查询返回9个字段（仓库、货道、货架、货位相关）
• $pickings 查询仅返回3个字段（拣货位相关）
  列数不一致直接导致了 Cardinality violation: 1222 错误。

方案1：修复UnionAll的列匹配问题 #

调整两个查询，让它们返回相同数量和顺序的字段，缺失字段用 null 填充：

use Illuminate\Support\Facades\DB;

// 调整拣货位查询，补充所有缺失字段，用null占位
$pickings = Warehouse::where('warehouses.id', $id)
    ->leftJoin('warehouse_pickings as wp', 'wp.warehouse_id', '=', 'warehouses.id')
    ->groupBy('wp.id')
    ->select(
        DB::raw('null as warehouse_id'),
        DB::raw('null as warehouse_name'),
        DB::raw('null as wi_id'),
        DB::raw('null as wi_type'),
        DB::raw('null as wi_name'),
        DB::raw('null as wr_id'),
        DB::raw('null as wr_name'),
        DB::raw('null as wb_id'),
        DB::raw('null as wb_name'),
        'wp.id as wp_id',
        'wp.type as wp_type',
        'wp.location as wp_location'
    );

// 调整仓库配置查询，补充拣货位相关字段
$config = Warehouse::leftjoin('warehouse_isles as wi', 'wi.warehouse_id', '=', 'warehouses.id')
    ->leftjoin('warehouse_racks as wr', 'wr.warehouse_isle_id', '=', 'wi.id')
    ->leftJoin('warehouse_bins as wb', 'wb.warehouse_rack_id', '=', 'wr.id')
    ->leftJoin('warehouse_pickings as wp', 'wp.warehouse_id', '=', 'warehouses.id')
    ->where('warehouses.id', $id)
    ->groupBy('wb.id', 'wr.id', 'wi.id', 'wp.id')
    ->select(
        'warehouses.id as warehouse_id',
        'warehouses.name as warehouse_name',
        'wi.id as wi_id',
        'wi.type as wi_type',
        'wi.name as wi_name',
        'wr.id as wr_id',
        'wr.name as wr_name',
        'wb.id as wb_id',
        'wb.name as wb_name',
        'wp.id as wp_id',
        'wp.type as wp_type',
        'wp.location as wp_location'
    );

$query = $config->unionAll($pickings);
return $query->paginate(5);

方案2：更优的单查询方案（推荐） #

从你期望的结果来看，实际需求是获取仓库下所有货道、货架、货位及其关联的拣货位数据（无关联则为null），完全可以通过一次多表关联查询实现，无需使用UnionAll：

$result = Warehouse::where('warehouses.id', $id)
    ->leftJoin('warehouse_isles as wi', 'wi.warehouse_id', '=', 'warehouses.id')
    ->leftJoin('warehouse_racks as wr', 'wr.warehouse_isle_id', '=', 'wi.id')
    ->leftJoin('warehouse_bins as wb', 'wb.warehouse_rack_id', '=', 'wr.id')
    ->leftJoin('warehouse_pickings as wp', function($join) {
        // 可根据实际业务调整关联逻辑，比如拣货位是否绑定到货位
        $join->on('wp.warehouse_id', '=', 'warehouses.id');
        // 如果拣货位与货位关联，添加：->on('wp.bin_id', '=', 'wb.id');
    })
    ->groupBy('wb.id', 'wr.id', 'wi.id', 'wp.id')
    ->select(
        'warehouses.id as warehouse_id',
        'warehouses.name as warehouse_name',
        'wi.id as wi_id',
        'wi.type as wi_type',
        'wi.name as wi_name',
        'wr.id as wr_id',
        'wr.name as wr_name',
        'wb.id as wb_id',
        'wb.name as wb_name',
        'wp.id as wp_id',
        'wp.type as wp_type',
        'wp.location as wp_location'
    )
    ->paginate(5);

return $result;

这个方案更简洁，避免了UnionAll带来的列匹配问题，也更贴合你的数据需求逻辑。

内容的提问来源于stack exchange，提问作者Sharif