Retrieve specific value on eloquent query

Question

I would like to get only some values on these 3 different models:

1 user can have 1 rank 1 user can have X tchatMessages

I query the model tchatMessage, passing by the model User and I access its Rank relation.

In tchatMessage I would like to get :

content
created_at

In User :

username

In Rank :

color
icon

Here is my current query which retrieves all the data from the three models:

return TchatMessage::with(['user' => function($user){
    $user->with(['rank'])->get();
}])->orderBy('created_at', 'desc')->limit(2)->get();

Relations :

In User :

public function tchatMessages()
{
  return $this->hasMany(TchatMessage::class);
}

In TchatMessage

public function user()
{
  return $this->belongsTo('App\Models\User');
}

In User to :

public function rank()
{
  return $this->belongsTo( 'App\Models\Rank' );
}

Edit 2:

This query works, I've the username only but all fields in rank, how can I select only color & icon in rank model ?

return TchatMessage::query()
            ->with(array('user' => function($query) {
                $query->select('id','rank_id','username')
                    ->with('rank');
            }))
            ->orderBy('created_at', 'desc')->limit(2)->get();

Edit 3: (works)

return TchatMessage::query()
            ->with(array('user' => function($query) {
                $query->select('id','rank_id','username')
                    ->with('rank:id,color,icon');
            }))
            ->select('content', 'user_id', 'created_at')
            ->orderBy('created_at', 'desc')->limit(2)->get();

user_13228383 · Answer

You can try to add select filter everywhere, like this :

return TchatMessage::query()
    ->select('content', 'created_at')
    ->with([
        'user' => function ($user) {
            $user->select('username');
        }, 
        'user.rank' => function ($rank) {
            $rank->select('color', 'icon');
        }])
    ->orderByDesc('created_at')
    ->limit(2)
    ->get();

And you can find more ways to do it, following this link

EDIT:

About your relations, following the official documentation, you can try to do like this :

User model :

public function tchatMessages()
{
    return $this->hasMany(TchatMessage::class);
}

public function rank()
{
    return $this->hasOne(Rank::class);
}

Rank model :

public function user()
{
    return $this->belongsTo(User::class);
}

TchatMessage model :

public function user()
{
    return $this->belongsTo(User::class);
}

EDIT 2:

With your query it works, so you can try :

return TchatMessage::query()
    ->select('content','created_at')
    ->with(['user:username' => function($user){
        $user->with(['rank:color,icon'])->get();
    }])->orderBy('created_at', 'desc')->limit(2)->get();

You can also let your query like this and add where conditions in your relations, something like this for example for you rank relation in your User model (if you don't need this relation somewhere else) :

public function rank()
{
    return $this->hasOne(Rank::class)->select(['color', 'icon']);
}

Retrieve specific value on eloquent query

1 Answers