php – Selecting specific columns from two related tables

Question:

I have a relationship of N x 1, Post x User , they have a relationship like the following:

Post.php (model):

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

What I want is to select the id and username of the User model when I access Post , but I also want to select just the id and title of the Post model (I don't want to select all columns).

With this solution :

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

Returns but the id and username of the User of each Post , then to select the id and the title of the Post tried:

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

But without success, returning the User to null. I am using laravel 5.5 if relevant.

Note: I don't want anything hard coded in the model file, because I might want different columns in different situations, I would rather like the relationship declared in Post.php be kept.

Answer:

The thing is, when Eloquent makes the relationship and loads the relationship, the keys that relate must be contained in the SQL results, because these values ​​are used to load the relationships, and apparently in your question this has been suppressed, or that is, the key was not mentioned in the SQL and this means not bringing the relation data, an example in SQL for you to have an idea of ​​how it works, example :

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

this command generates an SQL like this:

SELECT * FROM `posts`

in the result of this SQL brings all the user_id fields (default nomenclature of Eloquent ) with these values ​​and executes another SQL

SELECT * FROM `users` WHERE id IN [aqui todos os valores de `user_id`]

as in your question, not the key so it won't show anything, even after that you can't do the correlation with the results of the first SQL .

The solution

Pass the key in the Post select , example :

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

Using tinker, the result of all this was explained:

>>> App\Models\Post::with(['user' => function($q) { 
      return $q->select('id','name');}]
    )->select('id','title','user_id')->get();

string(44) "select `id`, `title`, `user_id` from `posts`"
array(0) { }
string(61) "select `id`, `name` from `users` where `users`.`id` in (?, ?)"
array(2) { [0]=> int(1) [1]=> int(2) }

Referencias:

Scroll to Top