Laravel Get data from relationship's relationship table and select column?

Question

I have 3 tables

Users Table

+----+-------+----------+
| id | email | password |
+----+-------+----------+
| 1  | 1     | John     |
+----+-------+----------+

user_details Table

+----+--------------------+------------+-----------+
| id | user_id [FK_users] | first_name | last_name |
+----+--------------------+------------+-----------+
| 1  | 1                  | John       | Kevin     |
+----+--------------------+------------+-----------+

Posts Table

+----+--------------------+----------+
| id | user_id [FK_users] | title    |
+----+--------------------+----------+
| 1  | 1                  | 1st Post |
+----+--------------------+----------+

Now i already created all the relationships in the models and now i can access the user_details table by below eloquent query it returns whole users table and user_details table but i only want to select first_name and last_name from user_details table how do i do that?

$posts= Post::with('city:id,name_en', 'user.userDetail')->where('id', $id)->get();

Nope i want to access user_details table's first_name & last_name column (there is more columns in user_details table i didn't added here)through user.userDetail relationship currently it returns whole columns from user_details and user table — Ahamed, Oct 01 '18 at 07:39
try with(['city:id,name_en', 'user.userDetail' => function($query){$query->select('first_name', 'last_name', 'id')}]); — Fatemeh Majd, Oct 01 '18 at 07:42
that because `user_id` must be in select query, since it is the foreign key. that's why result was null — Tharaka Dilshan, Oct 01 '18 at 07:52
@TharakaDilshan thanks it works but it returns whole user Table can't we only select id from users table or not select anything at all? — Ahamed, Oct 01 '18 at 08:03
@FatemegMajd Thanks it saved me i replaced id with user_id now it works but it returns whole user Table can't we only select id from users table or not select anything at all? — Ahamed, Oct 01 '18 at 08:04
@fatemehMajd Thank you very much for your effort some one answered below... — Ahamed, Oct 01 '18 at 08:30

score 1 · Accepted Answer · answered Oct 01 '18 at 08:23

1

this will fetch all the 'post' attributes, 'user' id, 'user_details' user_id, first_name, last_name

$posts= Post::with([
    'city:id,name_en',
    'user' => function ($query) {
        $query->select('id');
    },
    'user.userDetail' => function($query) {
        $query->select(['user_id', 'first_name', 'last_name']);
    }
])->where('id', $id)->get();

answered Oct 01 '18 at 08:23

Tharaka Dilshan

4,371
3
14
28

How to fetch only few attributes from "Post" in this case or set alias for few of columns in Post ? – Brainy Prb May 24 '20 at 17:12
fetch only few attributes: `Post::with(bla bla bla)->select([field_name1, field_name2])->where(bla bla bla);`. Don't forget to includes required foreign keys when selecting. – Tharaka Dilshan May 25 '20 at 15:07

Laravel Get data from relationship's relationship table and select column?

1 Answers1