CODEWITHSUNDEEP
laraveleloquentlaravel-8eloquent-relationship
sfarzoso
sfarzoso

Reputation: 1600

belongsToMany doesn't return the expected query

In my application I have a table called users, each user can have one or multiple roles (I'm using laratrust), in this scenario I will cover the tenant and the patient role.

The package santigator/laratrust handle the role within this tables:

Now, my app allows patient users to invite other users to register to the app, and they will automatically have the role of tenant. The patient user must also associate one or more riskareas for the invited user. In this way the "tenant" will have access only to certain areas of the patient user.

I handle the situation above in the invites table, this is the design to explain better:

enter image description here

The problem here is that we have a single users model that manages multiple roles (patient and tenant) and I have to make sure that the user model is able to return the tenants associated with a patient and vice versa.

So I though to declare in the User model two methods: patients() and tenants(), I started with the patients method:

public function patients()
{
    return $this->belongsToMany(User::class, 'invites')->withPivot('tenant_id');
}

when I do:

dd(auth()->user()->patients()->toSql());

It returns the following query:

"select * from `users` inner join `invites` on `users`.`id` = `invites`.`user_id` where `invites`.`user_id` = ? and `users`.`deleted_at` is null"

which returns an empty collection, the query should return this:

"select * from `users` inner join `invites` on `users`.`id` = `invites`.`user_id` where `invites`.`tenant_id` = ? and `users`.`deleted_at` is null"

I'm not sure if I'm handling this situation correctly. Someone could give me an help on this?

Kind regards

UPDATE

return $this->hasManyThrough(
    User::class,
    Invite::class,
    'tenant_id',
    'id',
    '123',
    'user_id'
);

RESULT:

"select * from `users` inner join `invites` on `invites`.`user_id` = `users`.`id` where `invites`.`tenant_id` is null and `users`.`deleted_at` is null and `invites`.`deleted_at` is null"

Why: where invites.tenant_id is null ?

Upvotes: 1

Views: 612

Answers (1)

Clément Baconnier
Clément Baconnier

Reputation: 6058

You have an issue with your foreign keys:

public function patients()
{
    return $this->belongsToMany(User::class,'invites', 'user_id', 'tenant_id');
}

More info about belongsToMany: https://laravel.com/docs/8.x/eloquent-relationships#many-to-many-model-structure

Upvotes: 1

Related Questions