how to properly show resource list with join or relation

I need to only show companies where user have access to in the list/table I tried doing this

    public static function getEloquentQuery(): Builder
    {
        if (auth()->user()->hasRole('super_admin')) {
            return parent::getEloquentQuery();
        }
        return parent::getEloquentQuery()
            ->select('companies.*')
            ->join('user_company', 'companies.id', '=', 'user_company.company_id')
            ->where('user_company.user_id',auth()->id());
    }

    public static function getEloquentQuery(): Builder
    {
        if (auth()->user()->hasRole('super_admin')) {
            return parent::getEloquentQuery();
        }
        return parent::getEloquentQuery()
            ->select('companies.*')
            ->join('user_company', 'companies.id', '=', 'user_company.company_id')
            ->where('user_company.user_id',auth()->id());
    }

but then on view/edit pages i get this error, how do i fix it or is there a better way ? SQLSTATE[HY000]: General error: 1 ambiguous column name: id (Connection: sqlite, SQL: select "companies".* from "companies" inner join "user_company" on "companies"."id" = "user_company"."company_id" where "user_company"."user_id" = 2 and "id" = 1 limit 1)

Solution:

```php public static function getEloquentQuery(): Builder { if (auth()->user()->hasRole('super_admin')) { return parent::getEloquentQuery();...

Jump to solution

1 Reply

Solution

Edward Kenway•3mo ago

php 
   public static function getEloquentQuery(): Builder
    {
        if (auth()->user()->hasRole('super_admin')) {
            return parent::getEloquentQuery();
        }
        return parent::getEloquentQuery()->whereHas('users', function ($query) {
            $query->where('user_id', auth()->id());
        });
    }

php 
   public static function getEloquentQuery(): Builder
    {
        if (auth()->user()->hasRole('super_admin')) {
            return parent::getEloquentQuery();
        }
        return parent::getEloquentQuery()->whereHas('users', function ($query) {
            $query->where('user_id', auth()->id());
        });
    }

could've used whereHas

Gaming

Programming

how to properly show resource list with join or relation