Topics

Filament•4w ago

How to only get list of tenants in the drop down if the user has any role to that tenant

Hello, I am using multi-tenancy in my project. I have around 3 tenants added and each user has different role with each tenant. I prevent user to login to the tenant login page if that user has no access to that tenant, but they can login to another tenant. But after they login to it they are presented with the tenants list drop down and it has all the tenants they are linked to. I want to hide the tenants from the drop down menu if the user has no role for that tenant.

public function getTenants(Panel $panel): Collection
    {
        return $this->companies;
    }

public function getTenants(Panel $panel): Collection
    {
        return $this->companies;
    }

the companies are the tenants in my project. I understand that whatever companies are returned in the above function will be listed in the dropdown, but unfortunately I can't filter the companies out which users are not allowed to access because of no roles assigned to them. Please let me know how do I make this work? Thank you so much.

35 Replies

Bruno Pereira•4w ago

You can create a function that returns a relation with the roles filtered.

public function userCompanies(): HasMany
{
  return $this->hasMany(...)->whereRelation(....)
OR
return $this->companies()->whereRelation(...)
}

public function userCompanies(): HasMany
{
  return $this->hasMany(...)->whereRelation(....)
OR
return $this->companies()->whereRelation(...)
}

Then on the

public function getTenants(Panel $panel): Collection
    {
        return $this->userCompanies;
    }

public function getTenants(Panel $panel): Collection
    {
        return $this->userCompanies;
    }

Something like that

mohdaftabOP•4w ago

@Bruno Pereira that is exactly what I am trying to create but somehow not getting it to work. I tried this but it doesn't seem to work

public function getTenants(Panel $panel): Collection
    {
        $this->companies->filter(function ($company) {
            return $this->hasAnyRoleInCompany($company);
        });
        return $this->companies;
    }


    public function hasAnyRoleInCompany($company): bool
    {
        return $this->roles()->where('company_id', $company->id)->exists();
    }

public function getTenants(Panel $panel): Collection
    {
        $this->companies->filter(function ($company) {
            return $this->hasAnyRoleInCompany($company);
        });
        return $this->companies;
    }


    public function hasAnyRoleInCompany($company): bool
    {
        return $this->roles()->where('company_id', $company->id)->exists();
    }

This is the error I get with the above functions

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'company_id' in where clause is ambiguous
select
  exists(
    select
      *
    from
      `roles`
      inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id`
    where
      `model_has_roles`.`model_id` = 4
      and `model_has_roles`.`model_type` = App \ Models \ User
      and `model_has_roles`.`company_id` = 2
      and (
        `roles`.`company_id` is null
        or `roles`.`company_id` = 2
      )
      and `company_id` = 1
  ) as `exists`

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'company_id' in where clause is ambiguous
select
  exists(
    select
      *
    from
      `roles`
      inner join `model_has_roles` on `roles`.`id` = `model_has_roles`.`role_id`
    where
      `model_has_roles`.`model_id` = 4
      and `model_has_roles`.`model_type` = App \ Models \ User
      and `model_has_roles`.`company_id` = 2
      and (
        `roles`.`company_id` is null
        or `roles`.`company_id` = 2
      )
      and `company_id` = 1
  ) as `exists`

Bruno Pereira•4w ago

How do you have your DB structured?

mohdaftabOP•4w ago

this is the model_has_roles table. we normally access the user roles for the selected tenant by using setPermissionsTeamId($company->id) otherwise it doesn't get the roles of that user for that particular company/tenant

No description

Bruno Pereira•4w ago

what about $this->companies()->whereRelation('roles','model_id',$this->id) you just want to get the companies where the user has a role, right?

mohdaftabOP•4w ago

yes let me try that Call to undefined method App\Models\Company::roles()

Bruno Pereira•4w ago

ok ok in your models do you have withPivot('company_id')?

mohdaftabOP•4w ago

for the User model?

Bruno Pereira•4w ago

or did you create a Model for the Pivot relation?

mohdaftabOP•4w ago

I have a pivot table but I didn't create model for pivot relations, how ever the company_id returns null for that user and pivot_company_id has the actual id

Bruno Pereira•4w ago

can you show the user model and company to check the relations?

mohdaftabOP•4w ago

wait please Here is the Company.php Model

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Laravel\Sanctum\HasApiTokens;

class Company extends Model
{
    use HasFactory, HasApiTokens;

    protected $fillable = [
        'name',
        'slug'
    ];

    protected $casts = [
        'email_notifications' => 'array',
        'address' => 'array',
    ];

    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class, "company_user");
    }

    public function vehicles(): HasMany
    {
        return $this->hasMany(Vehicle::class);
    }

    public function bookings(): HasMany
    {
        return $this->hasMany(Booking::class);
    }

    public function customers(): HasMany
    {
        return $this->hasMany(Customer::class);
    }

    public function emailTemplates(): HasMany
    {
        return $this->hasMany(EmailTemplate::class);
    }

    public function paymentGateways(): HasMany
    {
        return $this->hasMany(PaymentGateway::class);
    }
}

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Database\Eloquent\Relations\HasMany;
use Laravel\Sanctum\HasApiTokens;

class Company extends Model
{
    use HasFactory, HasApiTokens;

    protected $fillable = [
        'name',
        'slug'
    ];

    protected $casts = [
        'email_notifications' => 'array',
        'address' => 'array',
    ];

    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class, "company_user");
    }

    public function vehicles(): HasMany
    {
        return $this->hasMany(Vehicle::class);
    }

    public function bookings(): HasMany
    {
        return $this->hasMany(Booking::class);
    }

    public function customers(): HasMany
    {
        return $this->hasMany(Customer::class);
    }

    public function emailTemplates(): HasMany
    {
        return $this->hasMany(EmailTemplate::class);
    }

    public function paymentGateways(): HasMany
    {
        return $this->hasMany(PaymentGateway::class);
    }
}

mohdaftabOP•4w ago

Here is the User.php model

message.txt

mohdaftabOP•4w ago

sorry user model is bigger so it is attached as a file

Bruno Pereira•4w ago

Don't know if this works but return $this->companies()->whereRelation('users.roles','model_id',$this->id); If not you must query the roles table to return the company_ids and then search the Company model Company::where('id','IN',$companyIds)

mohdaftabOP•4w ago

this did not work. return $this->companies()->whereRelation('users.roles','model_id',$this->id); I am now trying to get the list of company ids for the 2nd method.

Bruno Pereira•4w ago

Or

$this->companies()->whereHas('users',function($query){
  $query->whereRelation('roles','model_id',$this->id)
});

$this->companies()->whereHas('users',function($query){
  $query->whereRelation('roles','model_id',$this->id)
});

mohdaftabOP•4w ago

this is what I get

App\Models\User::getTenants(): Return value must be of type Illuminate\Support\Collection, Illuminate\Database\Eloquent\Relations\BelongsToMany returned

App\Models\User::getTenants(): Return value must be of type Illuminate\Support\Collection, Illuminate\Database\Eloquent\Relations\BelongsToMany returned

Bruno Pereira•4w ago

what about with ->get() at the end?

mohdaftabOP•4w ago

let me try it worked but it fetched all companies with roles and without roles

Bruno Pereira•4w ago

Now you have to play with the conditions. Like $query->isNotNull or wtv can a user have a company without a role?

mohdaftabOP•4w ago

yes the users have a company without a role but they are not allowed to access the panel. But since they are attached to multiple tenants they can login using another panel where they have roles. The problem comes in when we have the list of tenants in the drop down, that is what is left to filter out, when the user clicks on the company that he has no roles with then it gives 404 error which is good, but it shouldn't really display the tenant link in the drop down in the first place.

Bruno Pereira•4w ago

so you need to add

$this->companies()->whereHas('users',function($query){
  $query->whereRelation('roles','model_id','=',$this->id);
  $query->whereRelation('roles','role_id','<>',null);
})->get();

$this->companies()->whereHas('users',function($query){
  $query->whereRelation('roles','model_id','=',$this->id);
  $query->whereRelation('roles','role_id','<>',null);
})->get();

something like that

mohdaftabOP•4w ago

The query looks so correct, but somehow it still gets that 3rd company which is not supposed to be there 😦

Bruno Pereira•4w ago

is the field of the role null in the DB?

mohdaftabOP•4w ago

not really, it just doesn't exists for that user with that company_id

mohdaftabOP•4w ago

the logged in user id is 4 and the company_id 1 and 2 it is clear in the table that the user has roles for companies with id 1 and 2 but the company with id 3 also appears

No description

Bruno Pereira•4w ago

instead of $this->companies() put Company:: query the class itself instead of the relation

mohdaftabOP•4w ago

ok same 😦

Bruno Pereira•4w ago

thats weird, the query should have worked :/ maybe someone with more understanding can help out

mohdaftabOP•4w ago

I will keep trying, may be there is something missing. Thank you so much for your time and efforts. Really appreciate it.

Bruno Pereira•4w ago

no prob, best of luck

mohdaftabOP•4w ago

@Bruno Pereira I think we were looking in the wrong table. The roles table doesn't have company id but model_has_roles table does. So I created a model named ModelHasRole and then used the query with it to get the desired results.

Bruno Pereira•4w ago

noice

mohdaftabOP•4w ago

Thank you so much

Filament is a collection of beautiful full-stack components for Laravel.You can get help using it on our Discord.

19KMembers

View on Discord

Did you find this page helpful?