Laravel nested relationships

Question

I'm having trouble getting a very-nested relationship to work correctly in laravel.

The wanted behaviour is as follows,

I select an event by ID and i want to see which persons are subscribed to it. Now the problem is there are some tables between the event and the person..

This is the query that works!

SELECT persons.id, 
       persons.firstname, 
       persons.lastname, 
       event_scores.score 
FROM   events 
       JOIN cities 
         ON cities.id = events.city_id 
       JOIN companies 
         ON cities.id = companies.city_id 
       JOIN persons 
         ON companies.id = persons.company_id 
       JOIN event_scores 
         ON event_scores.person_id = persons.id 
WHERE  event_scores.event_id = 1 
GROUP  BY persons.id

These are my relations

Event Model

class Event extends Eloquent
{
    protected $table = 'events';

    public function city()
    {
        return $this->belongsTo('City');
    }
}

City Model

class City extends Eloquent
{
    protected $table = 'cities';

    public function companies()
    {
        return $this->hasMany('Company');
    }

    public function event()
    {
        return $this->hasMany('Event');
    }
}

Company Model

class Company extends Eloquent {

    protected $table = 'companies';

    public function persons()
    {
        return $this->hasMany('Person');
    }

    public function city()
    {
        return $this->belongsTo('City');
    }
}

Person Model

class Person extends Eloquent
{
    protected $table = 'persons';

    public function company()
    {
        return $this->belongsTo('Company');
    }

    public function eventscore()
    {
        return $this->belongsToMany('Event', 'event_scores', 'person_id', 'event_id')
            ->withPivot('score')
            ->withTimestamps();
    }
}

What I have tried

return Event::with('city')->with('company')->get();

and

return Event::with('city')
    ->whereHas('companies', function($query) use ($company_id){
        $query->where('company_id', $company_id);
    })->get();

And many other possibilities, I'm really stuck on this. Is it so difficult in laravel to achieve this kind of nested relationship linking?

Thanks!

Joseph Silber · Accepted Answer · 2015-11-05T20:01:02.453

134

return Event::with('city.companies.persons')->get();

If you only want to select certain fields from the persons table, use this:

return Event::with(['city.companies.persons' => function ($query) {
    $query->select('id', '...');
}])->get();

edited Nov 05 '15 at 20:01

answered Sep 02 '14 at 17:13

Joseph Silber

214,931
59
362
292

Wow! That was fast! Didn't even know about this. Is it also possible to only select the fields i want? Cause it's returning a lot right now! Thank you so much! – Miguel Stevens Sep 02 '14 at 17:16
@MiguelStevens - The fields from what? The persons? – Joseph Silber Sep 02 '14 at 19:24
Yes, or any of the fields that are returned? Thanks a lot! – Miguel Stevens Sep 02 '14 at 19:45
3

@MiguelStevens `...->get(array('column1', 'column2'...));` Details are [here](http://laravel.com/api/source-class-Illuminate.Database.Query.Builder.html#933-944). – George Cummins Sep 03 '14 at 02:38
@GeorgeCummins AFAIK this only applies to the main model's table, right? I'm struggling to find a compact way to select the fields for the joined tables. – Cranio Dec 20 '14 at 17:21
12

How to get selective columns for city and companies ? – Sankalp Tambe Oct 10 '15 at 06:19
2

Hello, `$query->select('id', '...');` returns fields for last `with`. But how to return only last nested object from chain with? – Darama Feb 26 '17 at 09:45
So the relations goes like this: son table->father table->grandfather table; Hehe, thats a dummy way to understand :) – Luigi Lopez Jul 17 '18 at 02:58
How do you add the WHERE part here? @JosephSilber – Carmageddon Apr 03 '23 at 19:18
@Carmageddon - You want to add a `WHERE` to the top level, or the relationship? To add to the relationship, simply add a `where` call inside of the closure. To restrict the top-level based on the relationship, use a [`withWhereHas`](https://laravel.com/docs/10.x/eloquent-relationships#constraining-eager-loads-with-relationship-existence) instead of a regular `with`. – Joseph Silber Apr 05 '23 at 14:33

score 42 · Answer 2 · edited Apr 05 '23 at 19:31

42

For city and companies specific fields , you need to distribute the with eloquent. Eg:

return Event::with([
    'city' => function ($query) {
        $query->select('id', '...');
    },
    'city.companies' => function ($query) {
        $query->select('id', '...');
    },
    'city.companies.persons' => function ($query) {
        $query->select('id', '...');
    }
])->get();

Please note, that in nested levels (at least mid-levels), you MUST specify the FK to parent relationship as well, otherwise you'll get empty collection for that nested relationship! Took me hours to figure this out.

edited Apr 05 '23 at 19:31

Carmageddon

2,627
4
36
56

answered Apr 08 '16 at 11:42

Rashmi Nalwaya

486
4
7

What if I need to pass conditions on `Event` model too ? – Vineet Apr 10 '16 at 16:41
you can call **where** method before **get** method or you can call it before top level **with** method. – Rashmi Nalwaya Apr 11 '16 at 11:00
seem not working ? may be because of lacking square brace ? – Alex Aug 08 '16 at 07:37
awesome to know that I can pass an array to `with`! – chiliNUT Feb 14 '17 at 23:38
3

@RashmiNalwaya multiple chaining is not working for me ... say if i select city with some fields its working fine but if i add city.companies i am getting companies as null .. any idea why ?? – Shehzad Oct 06 '18 at 15:05
same as @Shehzad, I get null values in the deeper relations – Arnaud Bouchot Jul 03 '19 at 09:59
3

@arnaud-bouchot In city.companies relation, along with id please select foreign/reference keys as well. Eg: city_id – Rashmi Nalwaya Sep 18 '19 at 16:44
@RashmiNalwaya Can you update your answer to reflect the need for foreign keys to be selected as well? It will make your answer even more helpful, since what's shown now is close but not quite right (lacking the FKs). – gap Jul 08 '22 at 13:56
Answer edited to clarify this – Carmageddon Apr 05 '23 at 19:31

score 7 · Answer 3 · edited Apr 05 '23 at 19:32

7

return Event::with(['city:id,name', 'city.companies:id,name', 'city.companies.persons:id,name'])->get();

Please note, that in nested levels (at least mid-levels), you MUST specify the FK to parent relationship as well, otherwise you'll get empty collection for that nested relationship! Took me hours to figure this out.

edited Apr 05 '23 at 19:32

Carmageddon

2,627
4
36
56

answered Sep 18 '20 at 06:51

Sumit Verma

75
1
3

Selecting inside `with` doesnt seem to be working with 3 levels of nesting like that, if I specified fields on the second nesting like `city.companies:id,name`, then the companies level comes out as empty array. Any ideas? – Carmageddon Apr 05 '23 at 19:28

score 4 · Answer 4 · answered Nov 10 '21 at 00:46

4

for two level، in Event model

public function cities()
{
    return $this->belongsToMany(City::class)->with('companies');
}

answered Nov 10 '21 at 00:46

Avat Rezaei

143
9

1

This worked for me, to bring in a single query an object with its children and its grandchildren. – Codigo Morsa Jan 21 '22 at 12:20

score 2 · Answer 5 · answered Sep 26 '18 at 19:15

I created a HasManyThrough relationship for cases like this: Repository on GitHub

After the installation, you can use it like this:

class Event extends Model {
    use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

    public function persons() {
        return $this->hasManyDeep(
            Person::class,
            [City::class, Company::class],
            ['id'],
            ['city_id']
        );
    }
}

You can get attributes from intermediate tables with withIntermediate():

public function persons() {
    return $this->hasManyDeep(
        Person::class,
        [City::class, Company::class],
        ['id'],
        ['city_id']
    )->withIntermediate(City::class, ['id', '...']);
}

score 2 · Answer 6 · answered May 10 '19 at 07:47

To expand on @rashmi-nalwaya 's answer. I got it working for a 5.8 project with some tweaks.

My example was a bit different because I am trying to reference hasOne relations, rather than hasMany.

So for reference, Domains belong to one Website, which belongs to one Server. I only wanted to return certain columns from all of those tables. I had to do this.

Domain::with([
    'website' => function($q){
        $q->select('id', 'server_id');
    },
    'website.server' => function($q){
        $q->select('id', 'hostname', 'nickname');
    }
])
    ->select('id', 'website_id', 'domain')
    ->get();

Had to make sure I passed through the primary key for the table I'm on at any time (so the id in my case), and secondly, the foreign key of the related table I'm trying to get to. So website_id from domain, and server_id from website. Then it worked perfectly.

In my code I also have a further where clause on the main domain, after all this with-ness.

I also had to pass the foreign key through, but chose the simpler expression: `with(['website:id, server_id', 'website.server:id,hostname,nickname']) — gap, Jul 08 '22 at 13:48

score 0 · Answer 7 · answered Oct 09 '21 at 15:40

here is my project code, where I used this.

Checkout::where('cart_number', $cart_number)->with('orders.product')->first();

Result:

 "id": 23,
  "user_id": 4,
  "cart_number": "20219034",
  "phone_number": null,
  "mobile": "01533149024",
  "alternate_phone": "01533149024",
  "country_id": 19,
  "state_id": 750,
  "city_id": 8457,
  "address": "272/1-B, West Nakhalpara,Tejaon,Dhaka",
  "postal_code": "1215",
  "note": "dasd",
  "total": 974,
  "payment_type": "pending",
  "payment_status": 0,
  "courier_id": 3,
  "delivery_status": 0,
  "commented_by": null,
  "rating": null,
  "review": null,
  "coupon_code": null,
  "coupon_status": null,
  "created_at": "2021-10-09T14:59:46.000000Z",
  "updated_at": "2021-10-09T15:33:35.000000Z",
  "orders": [
    {
      "id": 32,
      "user_id": 4,
      "checkout_id": 23,
      "product_id": 2,
      "cart_number": 20219034,
      "courier_id": 3,
      "order_number": "202190340",
      "price": "554",
      "quantity": "1",
      "payment_type": "cod",
      "delivery_status": "pending",
      "created_at": "2021-10-09T14:59:46.000000Z",
      "updated_at": "2021-10-09T14:59:46.000000Z",
      "product": {
        "id": 2,
        "name": "Jasmine Bowers",
        "slug": "jasmine-bowers",
        "media_link": null,
        "description": null,
        "regular_price": 905,
        "sale_price": 554,
        "sku": "32312312",
        "have_stock": 1,
        "stock_quantity": 312,
        "stock_alert_quantity": 50,
        "weight": 5,
        "shipping_class_id": 1,
        "downloadable_file": null,
        "download_limit": null,
        "download_expiry": null,
        "short_description": null,
        "category": "[\"1\"]",
        "brand": 1,
        "tags": "[\"praesentium exceptur\"]",
        "product_image": "http://localhost/Bajaar/media/logo.png",
        "color": "[\"green\"]",
        "size": "[\"fugiat proident del\"]",
        "model": "[\"molestiae quia aute\"]",
        "other": "[\"corrupti enim illo\"]",
        "draft": 1,
        "uploaded_by": 1,
        "created_at": "2021-07-12T20:39:41.000000Z",
        "updated_at": "2021-07-12T20:39:41.000000Z"
      }
    }

score 0 · Answer 8 · answered Feb 04 '22 at 12:57

This is a problem that many devs will come accross, for that purpose ; You can chain relationships in whereRelation clause to mimic joins e.g

Payment::query()->whereRelation('orders.users',auth()->id());

The relations can continue to be nested with dot notation. Hope it saves you the hustle.