Limit attributes from joined tables in complex query build-CodePudding

So my question is a follow in from the one I posted here:

And using the suggestion in the accepted answer, this is what I ended up with

User::with(['profile'])->with('collections.game')
  ->whereHas('collections', function (Builder $query) {
    return $query->whereHas('game', function (Builder $query) {
      return $query->where('status', '<>', 'denied');
    });
  })
  ->paginate(10)

It works great. I get the information about the user from both the users and profiles table as well as all the collections the user owns of games that do not have a denied status. Sweet! Thanks @matiaslauriti!

The problem I'm seeing is that all of the columns are getting pulled in. This includes information about the user such as their email address (from the user's table) and their physical mailing address (from the profile table) among other things. Information that I would prefer not to disclose.

Under normal circumstances, this wouldn't be a problem because once the blade template was parsed and the markup for the page is generated, all of that data would get trashed and not get returned to the browser. But my circumstance is different. I'm using InertiaJS with React so all of that data is getting returned from the server via an XHR request and passed in as props to my component. This makes it so that all the data is completely visible and I definitely don't want that.

I've played around with ::without() and ::withOnly() but I couldn't get them to work. Not just not work for User but for the nested Profile data as well. So is there a way to do what I'm doing to get all the data I need but without all the sensitive data I'd rather not return to the client?

thnx,
Christoph

CodePudding user response：

It can be done one by passing a closure function in with() as second index of array.

Example:

Post::query()
    ->with(['user' => function ($query) {
        $query->select('id', 'username');
    }])
    ->get()

It will only select id and username from other table.

Remember that the primary key (id in this case) needs to be the first param in the $query->select() to actually retrieve the necessary results.*

CodePudding user response：

Calling the whereHas method will never select the related records. So if you want to run only the query on relations remove with in query

User::query()
    ->whereHas('collections', function (Builder $query) {
        return $query->whereHas('game', function (Builder $query) {
            return $query->where('status', '<>', 'denied');
        });
    })
    ->paginate(10);

You can also use dotted relation

User::query()
    ->whereHas('collections.game', function (Builder $query) {
        return $query->where('status', '<>', 'denied');
    })
    ->paginate(10);

CodePudding user response：

IF you are using a JSON response (e.g. you have an API) and IF you want to have a standard way to create "views" in your data for the purposes of e.g. displaying them in specific responses you can do that using JSON resources(if these two don't apply to you then you can ignore this answer)

In your case you could create two resources e.g. UserResource and ProfileResource and implement them as:

UserResource:

public function toArray($request)
{
    return [
        'id' => $this->id,
        'name' => $this->name,
        'profile' => new ProfileResource($this->profile),
    ];
}

ProfileResource:

public function toArray($request)
{
    return [
        'public_info' => $this->public_info,
    ];
}

As the docs mention you can send this response as:

$users = User::with(['profile'])->with('collections.game')
  ->whereHas('collections', function (Builder $query) {
    return $query->whereHas('game', function (Builder $query) {
      return $query->where('status', '<>', 'denied');
    });
  })
  ->paginate(10);
return UserResource::collection($users);