Select specific columns from two related tables

Question

I have an N x 1 relationship, Post x User, 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 the username of the model User when I access the Post, but I also want to select only the id and title of the model Post (do not want to select all columns).

With this solution:

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

Yes returns the id and username of User of each Post, to then select the id and the title of Post tried:

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

But without success, returning the User a null. I am using Laravel 5.5 if relevant.

Note: I don’t want anything 'hard coded' in the models file, because I may want different columns in different locations, preferably I would really like the ratio declared in Post.php remained.

Answer 1

I took the test with a 1xN relationship to simulate your problem and I believe your code is correct. Just need to insert foreign relationship key.

I would make a modification in arrays too

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

Another alternative you can choose is to create the query through the class DB. Let’s say the class Post is related to table posts and the class User is related to table users, you can write the same query above so:

DB::table('posts')
    ->join('users', 'posts.user_id', '=', 'users.id')
    ->select('users.id as user_id', 'users.username', 'posts.id as post_id', 'posts.title')
    ->get();

Answer 2

It’s like this, when the Eloquent makes the relationship and carries the relationship, the keys that relate should be contained in the results of the SQL, because, these values are used to load relationships, and apparently in your question this was deleted, ie the key was not mentioned in SQL and this implies not to bring the data of the relationship, 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 outcome of that SQL brings all the fields user_id (standard nomenclature of Eloquent) with these values and executes one more SQL

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

as in your question not the key so will not show anything even can not after this make the correlation with the results of the first SQL.

The solution

Pass the key on select of Post, example:

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

Using the Tinker the result of all this was what is 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) }

_References:

• Laravel - Tinker
• Laravel - Eloquent
• SQL