Eloquent whereHas not working as expected

I'm trying to search for some data on a pivot relationship using eloquent but I can't seem to get it to work as I want, this is my code so far

 $u = User::where('id', auth()->user()->id)
            ->with('apps')
            ->whereHas('apps', function (Builder $query) use ($app) {
                $query->where('sp_app_id', $app->id);
            })->first();

The pivot table is simple with only 3 fields: user_id, sp_app_id, and roles.

I would assume this would look in the relationship apps and only bring the one where the user_id matches the user and sp_app_id is equal to $app->id but its not, this is what this search is bringing back

{
   "id":1,
   "uuid":"15106044-d82a-11eb-b8bc-0242ac130003",
   "name":"Super Admin",
   "email":"[email protected]",
   "apps":[
      {
         "id":1,
         "image":null,
         "app_name":"App1",
         "pivot":{
            "user_id":1,
            "sp_app_id":1,
            "roles":"[{\"label\":\"Admin\",\"value\":2}]"
         }
      },
      {
         "id":2,
         "app_name":"App2",
         "pivot":{
            "user_id":1,
            "sp_app_id":2,
            "roles":"[{\"label\":\"Super Admin\",\"value\":1}]"
         }
      }
   ]
}

This would be my expected output

{
   "id":1,
   "uuid":"15106044-d82a-11eb-b8bc-0242ac130003",
   "name":"Super Admin",
   "email":"[email protected]",
   "apps":[
      {
         "id":1,
         "image":null,
         "app_name":"App1",
         "pivot":{
            "user_id":1,
            "sp_app_id":1,
            "roles":"[{\"label\":\"Admin\",\"value\":2}]"
         }
      }
   ]
}

What am I doing wrong? I want to access the string saved in apps.*.pivot.roles.

How can I ge this to work?

Sinnbeck

4 years ago

Level 102

You need to filter on the with as well. WhereHas only filters the "parent"

$u = User::where('id', auth()->user()->id)
            ->with(['apps' => function (Builder $query) use ($app) {
                $query->where('sp_app_id', $app->id);
            }] )
            ->whereHas('apps', function (Builder $query) use ($app) {
                $query->where('sp_app_id', $app->id);
            })->first();

Please or to participate in this conversation.