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

php laravel orm eloquent laravel-query-builder
Awais Qarni picture Awais Qarni · Nov 8, 2013 · Viewed 277.8k times · Source

I have two tables, User and Post. One User can have many posts and one post belongs to only one user.

In my User model I have a hasMany relation...

public function post(){
    return $this->hasmany('post');
}

And in my post model I have a belongsTo relation...

public function user(){
    return $this->belongsTo('user');
}

Now I want to join these two tables using Eloquent with() but want specific columns from the second table. I know I can use the Query Builder but I don't want to.

When in the Post model I write...

public function getAllPosts() {
    return Post::with('user')->get();
}

It runs the following queries...

select * from `posts`
select * from `users` where `users`.`id` in (<1>, <2>)

But what I want is...

select * from `posts`
select id,username from `users` where `users`.`id` in (<1>, <2>)

When I use...

Post::with('user')->get(array('columns'....));

It only returns the column from the first table. I want specific columns using with() from the second table. How can I do that?

Answer

Awais Qarni picture Awais Qarni · Nov 12, 2013

Well I found the solution. It can be done one by passing a closure function in with() as second index of array like

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

It will only select id and username from other table. I hope this will help others.

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.*

Related questions

Get the Query Executed in Laravel 3/4

How can I retrieve the raw executed SQL query in Laravel 3/4 using Laravel Query Builder or Eloquent ORM? For example, something like this: DB::table('users')->where_status(1)->get(); Or: (posts (id, user_id, ...)) User::find(1)…

Read More
Add a custom attribute to a Laravel / Eloquent model on load?

I'd like to be able to add a custom attribute/property to an Laravel/Eloquent model when it is loaded, similar to how that might be achieved with RedBean's $model->open() method. For instance, at the moment, in my …

Read More
How to Make Laravel Eloquent "IN" Query?

I want to make query in Laravel Eloquent like here its raw MySQL query SELECT * from exampleTbl where id in(1,2,3,4) I have tried this in Laravel Eloquent but it's not working DB::where("id IN(23,25)")->get()

Read More