May 8, 2015
0
Level 1
paginate on union
Hi,
I'm real problems getting pagination to work with unions, I have the following code which works, but can't get pagination to work
$hardware_items = \DB::table('hardware_items')
->select(\DB::raw("inventory_number, id, serial_number, name, 'hardware' AS 'type'"))
->where('inventory_number', 'LIKE', $search)
->orWhere('name', 'LIKE', $search)
->orWhere('serial_number', 'LIKE', $search);
$software_items = \DB::table('software_items')
->select(\DB::raw("inventory_number, id, serial_number, name, 'software' AS 'type'"))
->where('inventory_number', 'LIKE', $search)
->orWhere('name', 'LIKE', $search)
->orWhere('serial_number', 'LIKE', $search)->union($hardware_items)->get();
If I change it to ->paginate(4), I get the following error
SQLSTATE[21000]: Cardinality violation: 1222 The used SELECT statements have a different number of columns (SQL: (select count(*) as aggregate from `software_items` where `inventory_number` LIKE %foo% or `name` LIKE %foo% or `serial_number` LIKE %foo%) union (select inventory_number, id, serial_number, name, 'hardware' AS 'type' from `hardware_items` where `inventory_number` LIKE %foo% or `name` LIKE %foo% or `serial_number` LIKE %foo%))
any ideas?
Please or to participate in this conversation.