How to get the correct user from a pivot table

Some background Laravel 10.x mysql db Spatie Roles and permissions

so what i have is a many to many (states to representatives) there is 3 rep types at this point, with each type being its own role.

States table

public function reps()
{
        return $this->belongsToMany(User::class);
}

User table

public function repStates()
    {
        return $this->belongsToMany(State::class);
    }

State_rep

Schema::create('state_user', function (Blueprint $table) {
            $table->id();
            $table->foreignId('user_id')->constrained();
            $table->foreignId('state_id')->constrained();
            $table->timestamps();
        });

what i want is to say something like this

$state->regionalSalesRep();
$state->insideSalesRep()
$state->customerService()

currently i have states - reps - user - roles - user - roles - user - roles

I need to find a way to do this efficiently do im not sending out 180 queries per page load.

Snapey

3 years ago

Level 122

not sure where roles fit in, but I assume

$state->reps()->get();

would return up to 3 users? each having a different role?

Deveasey

3 years ago

Level 4

@Snapey yes they are preloaded relations so

$state->reps

returns a collection of 3 reps with roles (relation)

what i need is a way to say from the state reps give me the rep with the role of x with out a query. something like this.

$state->reps->where('role.name',  'Regional Sales')->get()

Deveasey

3 years ago

Level 4

Illuminate\Database\Eloquent\Collection {#1743 ▼ // resources/views/getInTouch/index.blade.php
  #items: array:3 [▼
    0 => App\Models\User {#1974 ▼
      #connection: "mysql"
      #table: "users"
      #primaryKey: "id"
      #keyType: "int"
      +incrementing: true
      #with: []
      #withCount: []
      +preventsLazyLoading: false
      #perPage: 15
      +exists: true
      +wasRecentlyCreated: false
      #escapeWhenCastingToString: false
      #attributes: array:19 [▶]
      #original: array:21 [▶]
      #changes: []
      #casts: array:4 [▶]
      #classCastCache: []
      #attributeCastCache: []
      #dateFormat: null
      #appends: []
      #dispatchesEvents: []
      #observables: []
      #relations: array:2 [▼
        "pivot" => Illuminate\Database\Eloquent\Relations\Pivot {#1975 ▶}
        "roles" => Illuminate\Database\Eloquent\Collection {#2350 ▼
          #items: array:2 [▼
            0 => Spatie\Permission\Models\Role {#2358 ▶}
            1 => Spatie\Permission\Models\Role {#2389 ▼
              #connection: "mysql"
              #table: "roles"
              #primaryKey: "id"
              #keyType: "int"
              +incrementing: true
              #with: []
              #withCount: []
              +preventsLazyLoading: false
              #perPage: 15
              +exists: true
              +wasRecentlyCreated: false
              #escapeWhenCastingToString: false
              #attributes: array:5 [▼
                "id" => 8
                "name" => "Customer Service"
                "guard_name" => "web"
                "created_at" => "2023-04-24 18:07:21"
                "updated_at" => "2023-04-24 18:07:21"
              ]
              #original: array:8 [▶]
              #changes: []
              #casts: []
              #classCastCache: []
              #attributeCastCache: []
              #dateFormat: null
              #appends: []
              #dispatchesEvents: []

Deveasey

3 years ago

Level 4

I got it working not very clean or elegant but it work, There has to be a better way of doing this but i like having the fields mapped that will make it easier to work with. Suggestions to make this cleaner/faster/more the Laravel way?

$states = State::with('reps.roles')->get();
        $states->transform(function ($item) {

            $state = [
                'id' => $item->getKey(),
                'name' => $item->name,
            ];

            foreach ($item->reps as $rep) {
                $role = $rep->roles->whereIn('name', [StateRep::REGIONAL_SALES, StateRep::INSIDE_SALES, StateRep::CUSTOMER_SERVICE])->first()->name;

                $state[$role] = $rep;
            }

            return $state;
        });

What "bad practices" am I following? and what would be better and why?

Please or to participate in this conversation.