How to query pivot table where clause?-CodePudding

I have 3 table's

File: id, name, document, language

Role: id, name (admin, partner, dealer, associate)

File_role: id, file_id, role_id

Now i want to display all the files for the 'dealer' role. i thought this should work:

$file_role = File_Role::where('role_id', '=', $id)->get('file_id');
        $file = File::where('id', '=', $file_role)->get();
        dd($file);

But this returns: SQLSTATE[HY093]: Invalid parameter number. the query it shows below that is: SELECT * FROM file WHERE id = { "file_id": 1 }

But i have no idea where to go, and i cant really find a working solution on the internet.

CodePudding user response：

I think its time for you to start using the Eloquent ORM. (https://laravel.com/docs/9.x/eloquent-relationships#querying-relations)

Solution example 1: (Query all files which has a relationship with Role where role's id equal to $id

// File model
public function roles() 
{
   return $this->belongsToMany(App\Models\Role::class, 'File_role');
}

// Query 
$files = File::whereHas('roles', function ($query) {
    $query->where('id', '=', $id);
})->get();

Solution example 2: (Query all files in relation to a given Role-model)

// Role model
public function files() 
{
   return $this->belongsToMany(App\Models\File::class, 'File_role');
}

// Query
$files = Role::find($id)->files;