[Help] - Database structure support

[mariomarquesdev]

Hello,

I would like to improve my eloquent model structure and i want to confirm if eloquent-has-many-deep package support my database structure.

At the moment i have following scenario: 3 tables (stores, store_members, store_member_managers)

Stores: id,name Store members: store_id (stores.id), user_id (users.id) Store member managers: member_id (store_members.id), manager_id (users.id)

Scenario 1: Want to get all managers from a store.

This is possible through the store_member_managers and store_members tables. As one user can be manager for multiple store_members i want to get distinct managers results. I accomplished that through an attribute from Store Model but i want to create a relationship for that. Can i achieve that with your package?

class Store extends Model
{
    public function getManagersAttribute()
    {
        return $this->hasManyThrough(StoreMemberManager::class, StoreMember::class, 'store_id', 'member_id')
            ->getQuery()
            ->select('manager_id')
            ->groupBy('manager_id')
            ->get();
    }
}

select `manager_id` from `store_member_managers` 
inner join `store_members` on `store_members`.`id` = `store_member_managers`.`member_id` 
where `store_members`.`store_id` = '15d96d38-4443-4e4e-9638-9e1acfbf8f9d' 
group by `manager_id`

With this package i already found a way to get all managers from a store but i have the same problem that the results are not distinct. Can i get unique values filtering 'manager_id' column and keep this a relationship?

use Staudenmeir\EloquentHasManyDeep\HasRelationships;

class Store extends Model
{
    use HasRelationships;

    public function managers()
    {
        return $this->hasManyDeep(StoreMemberManager::class, [StoreMember::class], [null, 'member_id']);
    }
}

Scenario 2: Want to get all members that a manager has access in a store.

At the moment i have the following relationship. This is returning all members that a manager has access but it's not including the store_id clause and it's a must in this case.

class StoreMemberManager extends Model
{
   public function managed()
   {
      return $this->hasManyThrough(StoreMember::class, StoreMemberManager::class, 'manager_id', 'id', 'manager_id', 'member_id');
   }
}

For this scenario the hasManyThrough is limited so i was trying to find a solution through this package. I already tried to achieve this with hasManyDeep but i couldn't find a way to do. I already tried multiples keys order but i can't get there.

use Staudenmeir\EloquentHasManyDeep\HasRelationships;

class StoreMemberManager extends Model
{
    use HasRelationships;

    public function managed()
    {
        return $this->hasManyDeep(
            StoreMember::class, 
            [StoreMember::class, Store::class, StoreMemberManager::class],
            [],
            []
        );
    }
}

Thank you, Mário

[staudenmeir]

Scenario 1: Want to get all managers from a store.

You can get distinct managers by "adding a step" and targeting the users table:

class Store extends Model
{
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function managers()
    {
        return $this->hasManyDeep(
            User::class,
            [StoreMember::class, StoreMemberManager::class],
            [null, 'member_id', 'id'],
            [null, null, 'manager_id']
        )->distinct();
    }
}

Scenario 2: Want to get all members that a manager has access in a store.

What does "has access" mean? Are store members employees?

[mariomarquesdev]

@staudenmeir

Thank you for your help. Your solution for the scenario 1 works like a charm.

What does "has access" mean? Are store members employees?

Yes and the managers are like supervisors. So with your solution i can now list the supervisors inside a store and for each supervisor i would like to list each member that he is supervising inside that store.

E.G:

stores:

id: 1 name: example

store_members:

id: 1 store_id: 1 (from stores above) user_id: 049c2f4e-d469-4e0a-bf91-9b7e314b92d1

store_member_managers:

id: 1 member_id: 1 (from store_members above) manager_id: 190db2c4-0a97-4afc-aba2-c2d21a86040c

With your solution for now i can do the following loop:

@foreach ($store->managers as $manager)
    {{ $manager->username }}
@endforeach

But for the scenario 2 i would like to list the members that a supervisor is supervising For instance:

@foreach ($store->managers as $manager)

    {{ $manager->username }}

    @foreach ($manager->managed as $member)
         {{ $member->username }}
    @endforeach

@endforeach

Previously i had the relation returning the StoreMemberManager and had a relation called user() belongsTo UserModel so in the view i was calling:

@foreach ($store->managers as $manager)
    {{ $manager->user->username }}
@endforeach

And with that i could have a relation in StoreMemberManager calling managed() and create a loop through there.

But with your solution in scenario 1 returning the User Model can i still manage to do this?

Thank you for your time, Mário

[staudenmeir]

What other columns besides username does the store_member_managers table have and what do they contain? It looks like your schema (and queries) might benefit from a refactoring/normalization (if that's still possible in your stage of development).

[mariomarquesdev]

What other columns besides username does the store_member_managers table have and what do they contain? It looks like your schema (and queries) might benefit from a refactoring/normalization (if that's still possible in your stage of development).

Username belongs to users table but with your solution above i can access the username right way:

public function managers()
{
    return $this->hasManyDeep(
        User::class,
        [StoreMember::class, StoreMemberManager::class],
        [null, 'member_id', 'id'],
        [null, null, 'manager_id']
    )->distinct();
}

Schema: users / stores / store_members / store_member_managers

users: id, username, avatar, first_name, ...., timestamps stores: id, name, timestamps store_members: id, store_id (references stores table), user_id (references users table), timestamps store_member_managers: id, member_id (references store_members table), manager_id (references users table), timestamps

What is your suggestion? And yes it is still possible in our stage.

[staudenmeir]

I would remove the store_member_managers table and instead move the manager_id column to store_members.

This way, you can use native BelongsToMany relationships for both scenarios:

class Store extends Model
{
    public function managers()
    {
        return $this->belongsToMany(
            User::class,
            'store_members',
            'store_id',
            'manager_id',
        )->distinct();
    }

    public function members()
    {
        return $this->belongsToMany(
            User::class,
            'store_members',
            'store_id',
            'user_id',
        );
    }
}

$managed = $store->members()->wherePivot('manager_id', $manager->id)->get();

You can also get the members of a manager and limit them by store:

class User extends Model
{
    public function members()
    {
        return $this->belongsToMany(
            User::class,
            'store_members',
            'manager_id',
        );
    }
}

$managed = $manager->members()->wherePivot('store_id', $store->id)->get();

[mariomarquesdev]

At the moment we need the store_member_managers table because one member can have one or many managers (I forgot to mention that, I'm sorry) that's why i was looking into your package that i think it can fit our structure.

[staudenmeir]

I would still consider the refactoring and create a record per manager in store_members.

The managers relationship from my last post still works and both members relationships only need an additional ->distinct().

[mariomarquesdev]

I think i understood your solution but i think we can't do that.

E.G: If i have a member with 100 managers for 1 member i would have 100 records in store_members only the manager_id would change. ( Will happen the same with the store_member_managers table but we have attributes in store_members for that specific store )

So in store_member for a member i would have repeated attributes and repeated information. For instance phone_number in store_members (It is a contact for that specific store and member) i would have 100 records with the same phone.

That's why i was looking into your hasManyDeep package and have the store_member_managers table

[staudenmeir]

From your comment I thought store_members only had these four columns.

At the moment i have the following relationship. This is returning all members that a manager has access but it's not including the store_id clause and it's a must in this case.

Do you mean that the store_id column is missing in the query results? Or is a WHERE constraint missing?

[mariomarquesdev]

Sorry for this. I didn't explain the all table structure.

From your comment I thought store_members only had these four columns.

It's a WHERE missing. I solved temporarily with the following solution:

/**
 * Managed.
 *
 * @todo: Improve this
 * @param string $store
 * @return void
 */
public function managed(string $store)
{
    return $this->hasMany(StoreMember::class, 'id', 'store_member_id')->where('store_id', $store);
}

But i don't like this approach because i have to do two queries. For example:

 $store = Store::find('307af698-ecfa-4635-b898-0813a8d127c9');
 dd($store->managers()->first()->managed($store->id)->get());

I was looking into something like this:

$store = Store::find('307af698-ecfa-4635-b898-0813a8d127c9');
dd($store->managers()->with(['managed'])->first());

[staudenmeir]

dd($store->managers()->first()->managed($store->id)->get()); dd($store->managers()->with(['managed'])->first());

What's the difference between these approaches? Both execute two queries.