Get Specific Columns Using “With()” Function in Laravel Eloquent

You can do it like this since Laravel 5.5:

Post::with('user:id,username')->get();

Care for the id field and foreign keys as stated in the docs:

When using this feature, you should always include the id column and any relevant foreign key columns in the list of columns you wish to retrieve.

For example, if the user belongs to a team and has a team_id as a foreign key column, then $post->user->team is empty if you don't specifiy team_id

Post::with('user:id,username,team_id')->get();

Also, if the user belongs to the post (i.e. there is a column post_id in the users table), then you need to specify it like this:

Post::with('user:id,username,post_id')->get();

Otherwise $post->user will be empty.

For loading models with specific column, though not eager loading, you could:

In your Post model

public function user()
{
    return $this->belongsTo('User')->select(['id', 'username']);
}

Original credit goes to Laravel Eager Loading - Load only specific columns

When going the other way (hasMany):

User::with(['post'=>function($query){
    $query->select('id','user_id');
}])->get();

Don't forget to include the foreign key (assuming it is user_id in this example) to resolve the relationship, otherwise you'll get zero results for your relation.

In Laravel 5.7 you can call specific field like this

$users = App\Book::with('author:id,name')->get();

It is important to add foreign_key field in the selection.

If you want to get specific columns using with() in laravel eloquent then you can use code as below which is originally answered by @Adam in his answer here in response of this same question, the answer's main code is as below :

Post::with('user:id,username')->get();

So i have used it in my code but it was giving me error of 1052: Column 'id' in field list is ambiguous, so if you guys are also facing same problem

Then for solving it you have to specify table name before the id column in with() method as below code:

Post::with('user:user.id,username')->get();

I came across this issue but with a second layer of related objects. @Awais Qarni's answer holds up with the inclusion of the appropriate foreign key in the nested select statement. Just as an id is required in the first nested select statement to reference the related model, the foreign key is required to reference the second degree of related models; in this example the Company model.

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

Additionally, if you want to select specific columns from the Post model you would need to include the user_id column in the select statement in order to reference it.

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

In your Post model:

public function userWithName()
{
    return $this->belongsTo('User')->select(array('id', 'first_name', 'last_name'));
}

Now you can use $post->userWithName

There is another alternative you can eager load specific columns

public function show(Post $post)
{
    $posts = $post->has('user')->with('user:id,name,email,picture')->findOrFail($post->id);
    return view('your_blade_file_path',compact('posts);
}

In your Post model you should have user relationship also

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

Note: It is mentioned in Laravel docs.

https://laravel.com/docs/8.x/eloquent-relationships#eager-loading-specific-columns

Note that if you only need one column from the table then using 'lists' is quite nice. In my case i am retrieving a user's favourite articles but i only want the article id's:

$favourites = $user->favourites->lists('id');

Returns an array of ids, eg:

Array
(
    [0] => 3
    [1] => 7
    [2] => 8
)

If you use PHP 7.4 or later you can also do it using arrow function so it looks cleaner:

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

I faced the same issue while using belongsToMany relationship with my user model (Laravel 8.x.x).

After a long search and trial and test method. I found out this answer

You have to make sure you are selecting the id's and any foreign keys that would be needed for the relationship from either side of that relationship. This allows Eloquent to match up parents to their children.

Original credit goes to https://stackoverflow.com/a/64233242/1551102

So I included

Groups::select('groupid')
...

And it worked like a charm. Although now I want to know how to hide the groupid field after fetching. I know I can simply loop through the array and remove it. But is there any other method? potentially a simpler and better one.

You can also specify columns on related model at the time of accessing it.

Post::first()->user()->get(['columns....']);

You can try this code . It is tested in laravel 6 version.

 public function getSection(Request $request)
{

  Section::with(['sectionType' => function($q) {
      $q->select('id', 'name');
  }])->where('position',1)->orderBy('serial_no', 'asc')->get(['id','name','','description']);
  return response()->json($getSection);
}

public function sectionType(){
    return $this->belongsTo(Section_Type::class, 'type_id');
}

Be careful that if you don't add the key column(s) it won't return anything. If you want to show only the username without the id you could instead define the $visible/$hidden properties within the Model, like so:

app/Models/User.php

protected $visible = ['username'];

Then it will retrieve only username column with:

Post::with('user')->get();

Hiding the key columns:

Alternatively you could hide the key column(s) and then retrieve only the columns you wish.

app/Models/User.php

protected $hidden = ['id'];

Specify which columns you want including the key or else it won't return anything, but this will actually only return the username, because id is $hidden.

Post::with('user:id,username')->get();

So this was my old code which is working fine but not as expected

$query = Book::withCount('bookReviews')
            ->withAvg('bookReviews', 'rating')
            ->with('createdBy:id,name')
            ->orderBy($sortField, $sortOrder);

meaning i am getting result like this

..................
{
    "id": 31,
    .....
    "created_by": {
        "id": 12,
        "name": "Darwin Grady"
    },
    .....
},
..................

But i only wanted to get the name of the created by user so i had to change it to

$query = Book::withCount('bookReviews')
            ->withAvg('bookReviews', 'rating')
            ->addSelect(['createdBy' => User::select('name')->whereColumn('id', 'books.created_by')])
            ->orderBy($sortField, $sortOrder);

Now this is the expected result

..................
{
    "id": 31,
    .........
    "createdBy": "Darwin Grady"
},
..................

Check this out : Advanced Subqueries

Now you can use the pluckmethod on a Collection instance:

This will return only the uuid attribute of the Post model

App\Models\User::find(2)->posts->pluck('uuid')
=> Illuminate\Support\Collection {#983
     all: [
       "1",
       "2",
       "3",
     ],
   }

Try with conditions.

$id = 1;
Post::with(array('user'=>function($query) use ($id){
    $query->where('id','=',$id);
    $query->select('id','username');
}))->get();

So, similar to other solutions here is mine:

// For example you have this relation defined with "user()" method
public function user()
{
    return $this->belongsTo('User');
}
// Just make another one defined with "user_frontend()" method
public function user_frontend()
{
    return $this->belongsTo('User')->select(array('id', 'username'));
}

// Then use it later like this
$thing = new Thing();
$thing->with('user_frontend');

// This way, you get only id and username, 
// and if you want all fields you can do this

$thing = new Thing();
$thing->with('user');

Sometimes, when creating a small application based on (a subset of) an existing database, it may be convenient to have all models fetch only the fields you need; even when loaded by with(). It may ease debugging and avoid confusion, to name an advantage. The '$visible' attribute (mentioned above) can only be used when the model is converted to array or JSON; see the Laravel docs. Otherwise, you could use a combination of local scopes and a 'public static $fields' property, as in the simplified 'User Class' example below.

class User extends Model
{
    public static $fields = [ 'id', 'full_name', 'image' ];
    
    public function scopeFields(Builder $query) : void {
        $query->select(self::$fields);
    }
    
    public function posts(): hasMany {
        return $this->hasMany(Post::class)->select(Post::$fields);
    }
}

Let's use the well-known class Post too:

class Post extends Model
{
    public static $fields = [ 'id', 'user_id', 'text', 'created_at' ];
    
    public function scopeFields(Builder $query) : void {
        $query->select(self::$fields);
    }

    public function user(): HasOne {
        return $this->hasOne(User::class)->select(User::$fields);
    }
}

As you can see, the functions scopeFields are identical, so you should put that in an in-between class. Anyway, after this you can use the models like:

User::fields()->with('posts')->get();
Post::fields()->with('user')->get();

All the returned objects will only contain the attributes that are listed in $fields. Tested with Laravel 10.

you must add foreign key in select

for example:

   public function expert(): HasOne
{
    return $this->hasOne(User::class, 'id', 'expert_id')
        ->select([
            'id',
            'name',
            'family',
            'image',
        ]);
}