Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Moseskamau338's avatar

Group by multiple columns at the same time

Hi, am trying to get rows from a sheduling table that have the same room_id, time, and day but all the groupBy() clouses I can think of are not working.... Please help me out.

Here's my code:

$allocations = RoomAssignment::all()->groupBY('room_id','day','time');

//result:
Illuminate\Database\Eloquent\Collection {#3185
     all: [
       6 => Illuminate\Database\Eloquent\Collection {#3191
         all: [
           App\RoomAssignment {#3132
             id: 1,
             user_id: 3,
             course_id: 10,
             room_id: 6,
             unit_id: 6,
             time: 11,
             day: "Teusday",
             duration: 3,
             created_at: "2020-04-10 10:16:27",
             updated_at: "2020-04-10 10:16:27",
           },
         ],
       },
       11 => Illuminate\Database\Eloquent\Collection {#3193
         all: [
           1 => App\RoomAssignment {#3169
             id: 2,
             user_id: 27,
             course_id: 5,
             room_id: 11,
             unit_id: 17,
             time: 11,
             day: "Wed",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           7 => App\RoomAssignment {#3183
             id: 8,
             user_id: 22,
             course_id: 5,
             room_id: 11,
             unit_id: 31,
             time: 11,
             day: "Thur",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           10 => App\RoomAssignment {#3172
             id: 11,
             user_id: 5,
             course_id: 5,
             room_id: 11,
             unit_id: 61,
             time: 11,
             day: "Wed",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
         ],
       },
       110 => Illuminate\Database\Eloquent\Collection {#3122
         all: [
           2 => App\RoomAssignment {#3151
             id: 3,
             user_id: 13,
             course_id: 5,
             room_id: 110,
             unit_id: 18,
             time: 8,
             day: "Tues",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           3 => App\RoomAssignment {#3186
             id: 4,
             user_id: 19,
             course_id: 5,
             room_id: 110,
             unit_id: 19,
             time: 14,
             day: "Fri",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
         ],
       },
       24 => Illuminate\Database\Eloquent\Collection {#3138
         all: [
           4 => App\RoomAssignment {#3177
             id: 5,
             user_id: 27,
             course_id: 5,
             room_id: 24,
             unit_id: 25,
             time: 8,
             day: "Thur",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           6 => App\RoomAssignment {#3195
             id: 7,
             user_id: 17,
             course_id: 5,
             room_id: 24,
             unit_id: 28,
             time: 8,
             day: "Tues",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           8 => App\RoomAssignment {#3153
             id: 9,
             user_id: 18,
             course_id: 5,
             room_id: 24,
             unit_id: 32,
             time: 11,
             day: "Mon",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           12 => App\RoomAssignment {#3134
             id: 13,
             user_id: 16,
             course_id: 8,
             room_id: 24,
             unit_id: 12,
             time: 11,
             day: "Tues",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           13 => App\RoomAssignment {#3159
             id: 14,
             user_id: 25,
             course_id: 8,
             room_id: 24,
             unit_id: 15,
             time: 14,
             day: "Mon",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
         ],
       },
       4 => Illuminate\Database\Eloquent\Collection {#3180
         all: [
           5 => App\RoomAssignment {#3142
             id: 6,
             user_id: 23,
             course_id: 5,
             room_id: 4,
             unit_id: 27,
             time: 14,
             day: "Tues",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           11 => App\RoomAssignment {#3179
             id: 12,
             user_id: 5,
             course_id: 8,
             room_id: 4,
             unit_id: 7,
             time: 11,
             day: "Thur",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           14 => App\RoomAssignment {#3144
             id: 15,
             user_id: 8,
             course_id: 8,
             room_id: 4,
             unit_id: 23,
             time: 8,
             day: "Fri",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           15 => App\RoomAssignment {#3148
             id: 16,
             user_id: 12,
             course_id: 8,
             room_id: 4,
             unit_id: 33,
             time: 11,
             day: "Thur",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
           16 => App\RoomAssignment {#3192
             id: 17,
             user_id: 22,
             course_id: 8,
             room_id: 4,
             unit_id: 48,
             time: 11,
             day: "Tues",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
         ],
       },
       109 => Illuminate\Database\Eloquent\Collection {#3190
         all: [
           9 => App\RoomAssignment {#3178
             id: 10,
             user_id: 24,
             course_id: 5,
             room_id: 109,
             unit_id: 35,
             time: 14,
             day: "Fri",
             duration: 3,
             created_at: "2020-04-13 21:42:51",
             updated_at: "2020-04-13 21:42:51",
           },
         ],
       },
     ],
   }

The result has groups of the same room_id which is great. But within the group, there are records that still have different time/day values. Am trying to remove conflicts within the schedules created.

0 likes
4 replies
bobbybouwmann's avatar
bobbybouwmann
6 years ago
Best Answer
Level 88

You need to group them in a special way. Right now the collection can only group on the first column. You need to make a combination of the 3 values to make them unique.

Try this instead

$allocations = RoomAssignment::all()->groupBy(function ($item, $key) {
    return sprint(
        '%s-%s-%s',
        $item->room_id,
        $item->day,
        $item->time,
    );
});

This way you create a group key that might look like this 6-Tuesday-11. This way the results should be grouped correctly ;)

Documentation: https://laravel.com/docs/7.x/collections#method-groupby

2 likes
Moseskamau338's avatar

It worked like a charm!.... I can't believe this... I even dreamed about it last night. THANK YOU!

Please or to participate in this conversation.