How to retrieve other models efficiently with the Many to Many pivot table

I'm building a dashboard which will be connected to multiple Environments.

An Environment has many Users
A User can belong to many Environments and they have a unique user ID for each Enviorment as set by the system it is connected to
Users can have multiple Enrollments in multiple Environments
An Enrollment is unique to a Environment

The Environment / Enrollment relationship is probably the easiest:

class Environment extends Model
{
    public function enrollments(): HasMany
    {
        return $this->hasMany(Enrollment::class);
    }
}

class Enrollment extends Model
{
    public function environment(): BelongsTo
    {
        return $this->belongsTo(Environment::class);
    }
}

For the User/Environment, I believe Many to Many is the most efficent:

class User extends Model
{
    public function environments(): BelongsToMany
    {
        return $this->belongsToMany(Environment::class);
    }
}

class Environment extends Model
{
    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class);
    }
}

With the pivot table containing the unique user id for that environment:

enviorment_user
    user_id - integer
    role_id - integer
    enviorment_user_id - integer

Assuming the above is correct, my question is:

How do I get the Enrollments of a User in a given Environment? The unique user ID (enviorment_user_id) is available both in the Pivot table and the in the Enrollment model.

As a mock up, this seems like a huge amount of overhead:

$user = User::find(1);

$environment = $user->environments->where('enviorment_id', 'abc')->first();

$unique_enviroment_user_id = $environment->pivot->enviorment_user_id;

$enrollments = $environment->enrollments()->where('unique_enviorment_user_id', $unique_enviorment_user_id)->get();

kevinbui

2 years ago

Level 41

The most efficient approach to me is to use the enrolments table is the intermediary table for a many to many relationship between environments and users (I believe Enrolment is the correct word).

public class User
{
    public function enrolments()
    {
        return $this->hasMany(Enrolment::class);
    }

    public function environments()
    {
        return $this->belongsToMany(Environment::class, 'enrolments');
    }
}

public class Environment
{
    public function enrolments()
    {
        return $this->hasMany(Enrolment::class);
    }

    public function users()
    {
        return $this->belongsToMany(User::class, 'enrolments');
    }
}

public class Enrolment
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function environment()
    {
        return $this->belongsToMany(Environment::class,);
    }
}

Then the enrolment of a user in a given environments:

return $user->enrolments()
    ->where('environment_id', $environment->id)
    ->first();

// Or
$user->enrolments
    ->first(fn (Enrolment $enrolment) => $enrolment->environment->is($environment));

Please or to participate in this conversation.