CODEWITHSUNDEEP
phpmysqldatabaselaravel
Miguel Stevens
Miguel Stevens

Reputation: 9191

Laravel nested relationships

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!

Upvotes: 68

Views: 99001

Answers (8)

Sumit Verma
Sumit Verma

Reputation: 75

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.

Upvotes: 7

Rashmi Nalwaya
Rashmi Nalwaya

Reputation: 516

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.

Upvotes: 44

Patrick
Patrick

Reputation: 7

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.

Upvotes: 0

Avat Rezaei
Avat Rezaei

Reputation: 153

for two level، in Event model

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

Upvotes: 5

Pri Nce
Pri Nce

Reputation: 711

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"
      }
    }

Upvotes: 0

John Halsey
John Halsey

Reputation: 2008

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.

Upvotes: 2

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

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', '...']);
}

Upvotes: 3

Joseph Silber
Joseph Silber

Reputation: 219920

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

Upvotes: 141

Related Questions