Select specific columns of two related tables

Question

Select specific columns of two related tables

Navigation

#1 by (1 votes)
#2 by (1 votes)

3

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

With this solution :

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

Return id and username of User of each Post , then select id and title of Post tried:

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

But unsuccessfully, returning User to null. I'm using laravel 5.5, if relevant.

Note: I do not want anything 'hard coded' in the models file, because I may want different columns in different situations, preferably liked even if the relation declared in Post.php would hold.

php laravel laravel-eloquent laravel-5

asked by anonymous 31.10.2017 / 21:55

2 answers

1

I tested with a 1xN relationship to simulate your problem and I believe your code is correct. You just need to enter the foreign key of the relationship.

I would make a modification to the arrays as well

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 DB class. Let's say the Post class is relative to the posts table and the User class is relative to the users table, you can write the same query as above:

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();

31.10.2017 / 23:01

Update table field just checked with checkbox php mysql Instantiate a class from its name in string

score 1 · Accepted Answer

It is the following, when Eloquent makes the relationship and carries the relationship, the keys that relate must be contained in the results of SQL , because these values are used to load the relationships, and apparently in your question this was suppressed, that is, the key was not mentioned in the SQL and this causes not to bring the data of the relation, an example in / p>

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

This command generates a SQL like this:

SELECT * FROM 'posts'

In the result of this SQL it brings all the fields user_id (default nomenclature of Eloquent ) to those 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 then it will not show anything even can not after that do correlation with the results of the first SQL .

The solution

Pass the key in 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 has been 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) }