Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel: need a combined relationship for account admins and their sub-users

I have Users. Some users (admins) manage accounts, defined via a account_user pivot table (account_id | admin_id). Some of these admins manage other "sub-users", defined via a user_management pivot table (account_admin_id | end_user_id).

#1) I need to create an Eloquent relationship to get all the sub-users of an account.

#2) I need to create an Eloquent relationship to get all users (admins and sub-users) of an account.

I have a method to get a Builder instance, but I really need to translate that into a relationship that I can pass around the app and call other methods on. I am able to get the relationship for the admins, of course. Here’s what I have (currently working):

class Account extends Model
{
    /**
     * @return Builder<User>
     */
    public function users():Builder
    {
        return User
            ::query()
            ->whereIn(
                'users.id',
                DB::table('account_user')
                  ->where('account_id', $this->id)
                  ->select('admin_id')
            )
            ->orWhereIn(
                'users.id',
                DB::table('end_user_management')
                  ->whereIn(
                      'account_admin_id',
                      DB::table('account_user')
                        ->where('account_id', $this->id)
                        ->select('admin_id')
                  )
                  ->select('end_user_id')
            );
    }

    /**
     * @return BelongsToMany<User>
     */
    public function admins():BelongsToMany
    {
        return $this->belongsToMany(
            related: User::class,
            table: 'account_user',
            foreignPivotKey: 'account_id',
            relatedPivotKey: 'admin_id',
            parentKey: 'id',
            relatedKey: 'id'
        );
    }
}

Really hope you can help. TIA!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Often, writing things out like this for strangers helps reveal unnecessary complexity in your system. By way of an answer that might help other folks, here is what we're changing.

    Both Account Admin and Sub-User roles have a many-to-many relationship with accounts. And (in our app) there's no reason any account admin shouldn't be able to manage any sub-user of their own account. So the additional end_user_management pivot is unnecessary; use account_user for everybody and let the app manage ownership and permissions logic based on the user's role.

    class Account extends Model
{
    /**
     * @return BelongsToMany<User>
     */
    public function users():BelongsToMany
    {
        return $this->belongsToMany(
            related: User::class,
            table: 'account_user',
            foreignPivotKey: 'account_id',
            relatedPivotKey: 'user_id',
            parentKey: 'id',
            relatedKey: 'id'
        );
    }

    /**
     * @return BelongsToMany<User>
     */
    public function admins():BelongsToMany
    {
        return $this->users()->where('role', '=' , 'admin');
    }

    /**
     * @return BelongsToMany<User>
     */
    public function subUsers():BelongsToMany
    {
        return $this->users()->where('role', '=' , 'sub-user');
    }
}

    We just simplified our query logic:

    $accounts = Account::withCount(['users', 'admins', 'subUsers'])->get();

...

@foreach ( $accounts as $account )
    <div>
        {{ $account->users_count }} Users:
        {{ $account->admins_count }} admins,
        {{ $account->sub_users_count }} sub-users
    </div>
@endforeach

  2. 0

    First, you need to create a relationship for the sub-users. This involves creating a custom relationship method that leverages Eloquent’s hasManyThrough relationship like this :

    class Account extends Model
{
    /**
     * Get all sub-users for the account.
     * 
     * @return IlluminateDatabaseEloquentRelationsHasManyThrough
     */
    public function subUsers(): HasManyThrough
    {
        return $this->hasManyThrough(
            User::class,
            EndUserManagement::class,
            'account_admin_id',  // Foreign key on the user_management table
            'id',                // Foreign key on the users table
            'id',                // Local key on the accounts table
            'end_user_id'        // Local key on the user_management table
        )
        ->whereIn(
            'account_admin_id',
            DB::table('account_user')
                ->where('account_id', $this->id)
                ->select('admin_id')
        );
    }
}

    Next, you need to create a relationship for all users (admins and sub-users) of an account. This can be achieved by combining the admins relationship with the subUsers relationship using a custom method like this:

    // Existing admins relationship
public function admins(): BelongsToMany
{
    return $this->belongsToMany(
        User::class,
        'account_user',
        'account_id',
        'admin_id'
    );
}

// Define subUsers relationship
public function subUsers(): HasManyThrough
{
    return $this->hasManyThrough(
        User::class,
        EndUserManagement::class,
        'account_admin_id',
        'id',
        'id',
        'end_user_id'
    )
    ->whereIn(
        'account_admin_id',
        DB::table('account_user')
            ->where('account_id', $this->id)
            ->select('admin_id')
    );
}

/**
 * Get all users (admins and sub-users) for the account.
 * 
 * @return IlluminateDatabaseEloquentRelationsBelongsToMany
 */
public function allUsers(): BelongsToMany
{
    $adminIds = $this->admins()->pluck('users.id');
    $subUserIds = $this->subUsers()->pluck('users.id');

    return User::whereIn('id', $adminIds->merge($subUserIds));
}

    And done for example you can get use it like this:

        $account = Account::find($accountId);

// Get all admins
$admins = $account->admins;

// Get all sub-users
$subUsers = $account->subUsers;

// Get all users (admins and sub-users)
$allUsers = $account->allUsers;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search