soulglow1985 soulglow1985 - 5 months ago 9
PHP Question

Returning Array in mysql Select

I'm trying to group inventory results by the model and manufacturer name, display the amount of items matching and 1 result per grouping. With that said, I'd like to try and retrieve all inventory id's within the group. Wondering if this is possible... Any ideas?

FYI - I'm using Laravel, the line in question is the ->selectRaw(CambridgeID as CambridgeIDArray)

$getMatchingInventory = DB::table('inventory')
->selectRaw('*, count(*) as groupTotal')
->whereRaw("MATCH(ManufacturerNameMatch, SubCategoryNameMatch, MainCategoryNameMatch, Model_Name, Title_Override, Description_Old) AGAINST ('$final' IN BOOLEAN MODE)")
->selectRaw('CambridgeID as CambridgeIDArray')
->groupBy('Model_Name', 'ManufacturerNameMatch')
->having('Units_OnHand', '>=', '1')
->orderBy('ManufacturerNameMatch')
//->paginate(15);
->get();

CSK CSK
Answer

try this

$getMatchingInventory = DB::table('inventory')
                            ->select(DB::raw("GROUP_CONCAT(CambridgeID) as `CambridgeIDArray`, count(*) as `groupTotal`"))
                            ->whereRaw("MATCH(ManufacturerNameMatch, SubCategoryNameMatch, MainCategoryNameMatch, Model_Name, Title_Override, Description_Old) AGAINST ('$final' IN BOOLEAN MODE)")
                            ->groupBy('Model_Name', 'ManufacturerNameMatch')
                            ->having('Units_OnHand', '>=', '1')
                            ->orderBy('ManufacturerNameMatch') 
                            ->get();