Best way to groupBy pivot table column

Hi,

I have 2 models with a many-to-many relationship. I am trying to group the data by a column that's inside the pivot table.

Here's an example with the structure of my database tables with different naming so it might be easier to understand.

classrooms

| id     | int | AI | PK |   |
|--------|-----|----|----|---|
| number | int |    |    |   |

teachers

| id         | int     | AI | PK |   |
|------------|---------|----|----|---|
| title      | varchar |    |    |   |
| first_name | varchar |    |    |   |
| last_name  | varchar |    |    |   |

classroom_teacher

| classroom_id | int      |   | PK | FK |
| teacher_id   | int      |   | PK | FK |
|--------------|----------|---|----|----|
| type         | int      |   |    |    |
| start_time   | datetime |   |    |    |
| end_time     | datetime |   |    |    |

In my Teacher Model I have

/**
     * Classroom relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function classrooms()
    {
        return $this->belongsToMany(Classroom::class)
            ->using(ClassroomTeacher::class)
            ->withPivot([
                'type',
                'start_time',
                'end_time'
            ]);
    }

In my Classroom Model I have

/**
     * Teacher relationship
     *
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function teachers()
    {
        return $this->belongsToMany(Teacher::class)
            ->using(ClassroomTeacher::class)
            ->withPivot([
                'type',
                'start_time',
                'end_time'
            ]);
    }

My ClassroomTeacher Model is just a custom Pivot Model so I can easily cast the dates, so nothing really going on here.

Now if I do a simple query like this it works fine.

Classroom::with([
    'teachers'
])
->get();

I ge the following result

[
  {
    "id": 1,
    "room": 403,
    "teachers": [
      {
        "id": 2,
        "title": "Drs",
        "first_name": "Jane",
        "last_name": "Doe",
        "pivot": {
          "type": "1",
          "start_time": "2023-01-11T11:00:00.000000Z",
          "end_time": "2023-01-11T12:00:00.000000Z"
        }
      },
      {
        "id": 3,
        "title": "Dr",
        "first_name": "John",
        "last_name": "Doe",
        "pivot": {
          "type": "1",
          "start_time": "2023-01-11T12:00:00.000000Z",
          "end_time": "2023-01-11T13:00:00.000000Z"
        }
      },
      {
        "id": 4,
        "title": "Dr",
        "first_name": "Jake",
        "last_name": "Doe",
        "pivot": {
          "type": "1",
          "start_time": "2023-01-11T13:00:00.000000Z",
          "end_time": "2023-01-11T14:00:00.000000Z"
        }
      }
    ]
  },
  {
    "id": 2,
    "room": 404,
    "teachers": [
      {
        "id": 2,
        "title": "Drs",
        "first_name": "Jane",
        "last_name": "Doe",
        "pivot": {
          "type": "1",
          "start_time": "2023-01-11T10:00:00.000000Z",
          "end_time": "2023-01-11T11:00:00.000000Z"
        }
      }
    ]
  }
]

Now I'm sending this to my Vue Front-end, and to display something them I would love to nest/group them by type.

E.g get the following:

[
  {
    "id": 1,
    "room": 403,
    "teachers": {
      "1": [
        {
          "id": 2,
          "title": "Drs",
          "first_name": "Jane",
          "last_name": "Doe",
          "pivot": {
            "type": "1",
            "start_time": "2023-01-11T11:00:00.000000Z",
            "end_time": "2023-01-11T12:00:00.000000Z"
          }
        },
        {
          "id": 4,
          "title": "Dr",
          "first_name": "Jake",
          "last_name": "Doe",
          "pivot": {
            "type": "1",
            "start_time": "2023-01-11T13:00:00.000000Z",
            "end_time": "2023-01-11T14:00:00.000000Z"
          }
        }
      ],
      "2": [
        {
          "id": 3,
          "title": "Dr",
          "first_name": "John",
          "last_name": "Doe",
          "pivot": {
            "type": "2",
            "start_time": "2023-01-11T12:00:00.000000Z",
            "end_time": "2023-01-11T13:00:00.000000Z"
          }
        }
      ]
    }
  }
]

As you see, inside the classroom teachers relationship, the teachers are grouped by the type.

What I tried to do is.

Classroom::with([
    'teachers' => function ($teacherQuery) {
        $teacherQuery->groupBy('pivot_type');
    }
])
->get();

But I receive the follwing error

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'table.teachers.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I know I can fix only_full_group_by by disabling it with

mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Or setting the

'mysql' => [
    'strict' => false
],

in config/database.php

But obviously, this isn't the right thing to do, as this can cause issues from my understanding.

I know it's because something is wrong in my query, but I'm not exactly sure how to fix this. Any tips?