Quering from DB using Wherehas manyTomany laravel

I am trying to retrieve data using wharehas + whereIn but it retrieve all data in pivot table

Project Model

public function resources(){
			return $this->belongsToMany(Resource::class)->withPivot('id','value');
}

Project Controller

 $requestedIds      // array of ids 
 $projects= Project::select('projects.*')
      ->with('resources')
      ->whereHas('resources', function($q) use($requestedIds ) {
            $q->whereIn('resource_id', $requestedIds );
      })->get();

I need to retrieve project [requested] resources only not all resources relaited to project

LaryAI

3 years ago

Level 58

You can use the wherePivot method to filter the related resources. The wherePivot method allows you to add constraints to the relationship's pivot table:

$projects= Project::select('projects.*')
      ->with('resources')
      ->whereHas('resources', function($q) use($requestedIds ) {
            $q->whereIn('resource_id', $requestedIds )
              ->wherePivot('value', '=', 'requested');
      })->get();

This will filter the related resources to only those with a value of requested.

_fag

3 years ago

Level 11

Try to remove the "select" and the "with" from the query.

Project::whereHas('resources', function ($query) {
    return $query->whereIn('resource_id', $requestedIds);
})->get();

Please or to participate in this conversation.