Get only one column from relation

Question

I have found this: Get Specific Columns Using “With()” Function in Laravel Eloquent but nothing from there did not help.

I have users table, columns: id , name , supplier_id. Table suppliers with columns: id, name. When I call relation from Model or use eager constraints, relation is empty. When I comment(remove) constraint select(['id']) - results are present, but with all users fields.

$query = Supplier::with(['test_staff_id_only' => function ($query) {
            //$query->where('id',8); // works only for testing https://laravel.com/docs/6.x/eloquent-relationships#constraining-eager-loads
            // option 1
            $query->select(['id']); // not working , no results in // "test_staff_id_only": []
            // option 2
            //$query->raw('select id from users'); // results with all fields from users table
        }])->first();
        return $query;

In Supplier model:

public function test_staff_id_only(){
        return $this->hasMany(User::class,'supplier_id','id')
            //option 3 - if enabled, no results in this relation
            ->select(['id']);// also tried: ->selectRaw('users.id as uid from users') and ->select('users.id')
}

How can I select only id from users?

see @OMR answer and not forget "eager loading won't work without foreingkey relation" — Vit, Jun 05 '20 at 16:57

OMR · Accepted Answer · 2020-06-05T16:53:44.107

2

in you relation remove select(['id'])

public function test_staff_id_only(){
        return $this->hasMany(User::class,'supplier_id','id');
}

now in your code:

$query = Supplier::with(['test_staff_id_only:id,supplier_id'])->first();

edited Jun 05 '20 at 16:53

answered Jun 05 '20 at 16:50

OMR

11,736
5
20
35

This is what suggested Tim and it not works ( I mean it not limiting columns, and shows all columns from users table ) – Vit Jun 05 '20 at 16:51
this column 'supplier_id' what is his table name? – OMR Jun 05 '20 at 16:52
if I remove select(['id']) - i get all columns. – Vit Jun 05 '20 at 16:52
users: id, name, supplier_id and suppliers table: id, name – Vit Jun 05 '20 at 16:53
2

eager loading won't work without foreingkey relation – OMR Jun 05 '20 at 16:54
Thank you very much! – Vit Jun 05 '20 at 16:59
Ah, so you need both `:id,supplier_id`; that makes sense. So my approach was still correct, but slightly off. Good to know. – Tim Lewis Jun 05 '20 at 17:15

score 1 · Answer 2 · answered Jun 05 '20 at 16:29

1

There's a pretty simple answer actually. Define your relationship as:

public function users(){
  return $this->hasMany(User::class, 'supplier_id', 'id');
}

Now, if you call Supplier::with('users')->get(), you'll get a list of all suppliers with their users, which is close, but a bit bloated. To limit the columns returned in the relationship, use the : modifier:

$suppliersWithUserIds = Supplier::with('users:id')->get();

Now, you will have a list of Supplier models, and each $supplier->users value will only contain the ID.

answered Jun 05 '20 at 16:29

Tim Lewis

27,813
13
73
102

Not working. "users" are empty , and checked again with Heidi: user with id 8 is having supplier_id 1. – Vit Jun 05 '20 at 16:33
if I remove ":id" from "users:id" - it shows user data with all users columns – Vit Jun 05 '20 at 16:34
Maybe needs to be `users:supplier_id`? Either way the principle stands; to limit the columns returned from the relationship, you use the `:` modifier. – Tim Lewis Jun 05 '20 at 16:36
No. I need id's of users (theirs ids) – Vit Jun 05 '20 at 16:38
if make pivot table with columns user_id and supplier_id and use in model this: public function staff_id_only() { return $this ->belongsToMany('App\User')->select(['users.id']); } then it works! But I want to remove that pivot table. Also tried ->select(['users.id']) in my attempts (with hasMany) but not successfull – Vit Jun 05 '20 at 16:41
Use ```pluck()``` to achieve this, ex: ```Suppliers::all()->pluck('id')```. This will return all suppliers ids. See more in the Laravel doc: https://laravel.com/docs/7.x/collections#method-pluck – ettdro Jun 05 '20 at 16:47
@ettdro no, I want users id's in relation , not id of Supplier. – Vit Jun 05 '20 at 16:49

Get only one column from relation

2 Answers2