Laravel Union and pagination

I am trying to achieve a result using Laravel Union.

[
 {
  warehouse_id: 12,
  warehouse_name: "new",
  wi_id: 19,
  wi_type: "storage",
  wi_name: "1",
  wr_id: 20,
  wr_name: "1",
  wb_id: 3,
  wb_name: "1",
  wp_id:null,
  wp_type:null,
  wp_location:null,
 },
 {
  warehouse_id: 12,
  warehouse_name: "new",
  wi_id: 19,
  wi_type: "storage",
  wi_name: "1",
  wr_id: 20,
  wr_name: "1",
  wb_id: 6,
  wb_name: "2",
  wp_id:"1",
  wp_type:"fixed",
  wp_location:"hellow",
 }
]

To have the solution I have used two queries and merge them together, I want to have the result in one single query but no luck…

$pickings = Warehouse::where('warehouses.id', $id)
            ->leftJoin('warehouse_pickings as wp', 'wp.warehouse_id', '=', 'warehouses.id')
            ->groupBy('wp.id')
            ->select(
                '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')
            ->where('warehouses.id', $id)
            ->groupBy('wb.id', 'wr.id', 'wi.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',
            );
$query = $config->unionAll($pickings);
return $query->paginate(5);

The error I am having
SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns

Any help will be greatly appreciated.

Thanks

>Solution :

Identifying problem

The error you are getting is due to the fact that the two queries in your UNION statement do not have the same number of columns. When using UNION or UNION ALL, you have to make sure that both SELECT statements have the same number of columns, and those columns should also be of similar data types.

Explanation

It looks like you are trying to join four tables together: warehouses, warehouse_pickings, warehouse_isles, warehouse_racks, and warehouse_bins. Your SELECT statements in each of your subqueries should also have the same number of columns. But in your case, the second subquery selects 9 columns while the first one selects only 3.

Solution

You should add the missing columns in the first query and fill them with NULL, so that it matches with the columns in the second query. This way, you are tricking the system into thinking that the two subqueries have the same structure.

Here’s how you can achieve this:

$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')
            ->where('warehouses.id', $id)
            ->groupBy('wb.id', 'wr.id', 'wi.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',
                DB::raw('null as wp_id'),
                DB::raw('null as wp_type'),
                DB::raw('null as wp_location')
            );

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