Laravel - how to group by pivot table column

Question

I have a many to many relationship with one extra column in pivot table i.e module_id, i want to eager load the relationship data groupBy('module_id'). I tried couple of thing that didn't work, I successfully managed to get the desired structure but not through the eager loading (right way), i directly called the pivot table model queried the table. The problem is i cannot query pivot table directly becuase i have implemented the repository pattern.

This is how i queries the pivot directly.

$selectedGroups = RoleGroup::select('role_id', 'module_id', 'group_id')->where('role_id', $role->id)->get()->groupBy('module_id');

Migration:

Schema::create('role_groups', function (Blueprint $table)
{
    $table->id();
    $table->unsignedBigInteger('role_id');
    $table->unsignedBigInteger('module_id');
    $table->unsignedBigInteger('group_id');
    $table->timestamps();
                
    $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
    $table->foreign('group_id')->references('id')->on('groups')->onDelete('cascade');
});

Role Model:

public function groups()
{
    return $this->belongsToMany(Group::class, 'role_groups')->withPivot('module_id')->withTimestamps();
}

Group Model:

public function roles()
{
    return $this->belongsToMany(Role::class, 'role_groups')->withPivot('module_id')->withTimestamps();
}

Desired Structre:

The keys are module_id and the values inside are group_id

{
    "1": [
        1
    ],
    "2": [
        1
    ],
    "3": [
        1
    ],
    "4": [
        1
    ],
    "5": [
        1,
        2,
        3,
        4
    ]
}

What i got:

{
    "1": [
        {
            "role_id": 1,
            "module_id": 1,
            "group_id": 1
        },
        {
            "role_id": 1,
            "module_id": 1,
            "group_id": 3
        }
    ],
    "2": [
        {
            "role_id": 1,
            "module_id": 2,
            "group_id": 1
        },
        {
            "role_id": 1,
            "module_id": 2,
            "group_id": 3
        }
    ],
    "3": [
        {
            "role_id": 1,
            "module_id": 3,
            "group_id": 1
        }
    ],
    "5": [
        {
            "role_id": 1,
            "module_id": 5,
            "group_id": 1
        }
    ]
}

Answer 1

You may achieve this by grouping by multiple columns. i.e:

RoleGroup::query()
    ->select("module_id", "group_id")
    ->where("role_id", $role->id)
    ->groupBy("module_id", "group_id")
    ->get()
    ->reduce(
        function (array $carry, Model $item) {
            $carry[$item->module_id][] = $item->group_id;
            return $carry;
        }
        , []);

References:
reduce()
groupBy()