I have the below 3 tables:
posts<--(hasMany)--users--(hasOne)-->user_info
-------------------------------------------------
Post
-------------------------------------------------
id | user_id | content | created_at | updated_at
----------------------------------------------------------------------------------------
users
----------------------------------------------------------------------------------------
id | user_info_id | email| username | password | access_token | created_at | updated_at
------------------------------------------------------------------------------------------------------------
user_info
------------------------------------------------------------------------------------------------------------
id | user_id | name | web | birthday | gender | bio_description | profile_picture | created_at | updated_at
The relationships stablished as below:
User.php
...
public function userInfo() {
return $this->hasOne('App\UserInfo');
}
...
public function posts() {
return $this->hasMany('App\Post');
}
...
UserInfo.php
...
public function user() {
return $this->belongsTo('App\User');
}
...
Post.php
...
public function user() {
return $this->belongsTo('App\User');
}
...
And I get all the posts with the user and user_info nested data like this:
Post::with('user.userInfo')
which returns something like this:
{
"data": [
{
"id": 1,
"created_at": "2020-08-01 06:10:00",
"updated_at": "2020-08-01 06:10:00",
"user_id": 1,
"content": "My first post!",
"user": {
"id": 1,
"user_info_id": 1,
"email": "myemail@gmail.com",
"username": "derek",
"access_token": "secret",
"created_at": "2020-08-01 04:15:09",
"updated_at": "2020-08-01 04:15:09",
"user_info": {
"id": 1,
"user_id": 1,
"name": "Derek Baker",
"web": "https://github.com/derek90",
"birthday": "1990-11-27",
"gender": "M",
"bio_description": "Software Developer",
"profile_picture": null
}
}
},
{
"id": 2,
"created_at": "2020-08-01 06:09:54",
"updated_at": "2020-08-01 06:09:54",
"user_id": 1,
"content": "My second post!",
"user": {
"id": 1,
"user_info_id": 1,
"email": "myemail@gmail.com",
"username": "derek",
"remember_token": null,
"access_token": "secret",
"created_at": "2020-08-01 04:15:09",
"updated_at": "2020-08-01 04:15:09",
"user_info": {
"id": 1,
"user_id": 1,
"name": "Derek Baker",
"web": "https://github.com/derek90",
"birthday": "1990-11-27",
"gender": "M",
"bio_description": "Software Developer",
"profile_picture": null
}
}
}
]
}
What I want, is to obtain just a few columns of each entity, like so:
{
"data": [
{
"id": 1,
"created_at": "2020-08-01 06:10:00",
"updated_at": "2020-08-01 06:10:00",
"content": "My first post!",
"user": {
"id": 1,
"username": "derek",
"user_info": {
"name": "Derek Baker",
"profile_picture": null
}
}
},
{
"id": 2,
"created_at": "2020-08-01 06:09:54",
"updated_at": "2020-08-01 06:09:54",
"content": "My second post!",
"user": {
"id": 1,
"username": "derek",
"user_info": {
"name": "Derek Baker",
"profile_picture": null
}
}
}
]
}
Is there a way to achieve this using Post::with Eloquent function?
I already tried with Post::with('user:id,username', 'user.userInfo') which works well for the user columns, but userInfo brings all of its.
Other things I've tried:
Post::with('user:id,username', 'user.userInfo:name,profile_picture') which brings "user_info": null in the json field
Post::with('user:id,username', 'user.userInfo:userInfo.name,userInfo.profile_picture') which show the error Unknown column 'userInfo.name' in 'field list'
The same error is thrown using user.userInfo:user.userInfo.name,user.userInfo.profile_picture and user.userInfo:user.user_info.name,user.user_info.profile_picture
You can use API Resources https://laravel.com/docs/7.x/eloquent-resources#introduction
API Resource acts as a transformation layer that sits between your Eloquent models and the JSON responses that are actually returned to your application's users.
You may create an API resource for the post and use it wherever you're returning the post in the response.
Api resources gives you a lot more control, you could manipulate whatever field you want, send some extra fields using the combination of a few fields, change the name of the fields that you want in your response (xyz => $this->name)
PostResource
<?php
namespace App\Http\Resources;
use Illuminate\Http\Resources\Json\JsonResource;
class PostResource extends JsonResource
{
public function toArray($request)
{
//You can access model properties directly using $this
return [
"id" => $this->id,
"created_at" => $this->created_at,
"updated_at" => $this->updated_at,
"content" => $this->content,
"user" => [
"id" => $this->user->id,
"username" => $this->user->username,
"user_info" => [
"name" => $this->user->userInfo->name,
"profile_picture" => $this->user->userInfo->profile_picture,
]
]
];
}
}
And then wherever you want to return a post as a response.
Controller
// $post is a Post Model Instance.
return new PostResource($post);
In case you have a collection
// $posts is a collection of Post Model instances.
return PostResource::collection($posts);
PostResource will be applied for every model instance in your collection and then returned to you as your JSON response.
[NOTE]
You may create similar resource for users, and any model. It makes it easier to customize the response as you like.
Also, in the above example. Instead of having user_info inside user, you could simply add the user_info properties to the user only.
return [
"id" => $this->id,
"created_at" => $this->created_at,
"updated_at" => $this->updated_at,
"content" => $this->content,
"user" => [
"id" => $this->user->id,
"username" => $this->user->username,
"name" => $this->user->userInfo->name,
"profile_picture" => $this->user->userInfo->profile_picture,
]
];