Returning Array in mysql Select

Question

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();

Can't you put Units_OnHand in the WHERE statement, btw? See below for your answer (GROUP_CONCAT is what you are looking for). — ldg
– ldg, Commented Jul 6, 2016 at 20:20
@ldg thank you for that recommendation, that helps clean it up a bit. — soulglow1985
– soulglow1985, Commented Jul 6, 2016 at 20:35
It should make it slightly faster too, as might grouping by id instead of string and creating a composite index on those columns, just depends the size and needs of your db. — ldg
– ldg, Commented Jul 6, 2016 at 21:01

CSK · Accepted Answer · 2016-07-06 20:11:36Z

2

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();

answered Jul 6, 2016 at 20:11

CSK

3521 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ldg · Accepted Answer · 2016-07-06 20:06:59Z

1

You should be able to use GROUP_CONCAT for that, see: http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

You can use specify output format options (e.g., SEPARATOR) and use additional string manipulation as needed within the GROUP_CONCAT.

(Fyi, using raw MySQL, at least for this question, would make it easier to parse.)

answered Jul 6, 2016 at 20:06

ldg

9,4523 gold badges32 silver badges47 bronze badges

1 Comment

soulglow1985 Over a year ago

thanks again for the documentation and helpful response.

Collectives™ on Stack Overflow

Returning Array in mysql Select

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related