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!

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

I created a `HasManyThrough` relationship for cases like this: [Repository on GitHub][1]

After the installation, you can use it like this:

<!-- language-all: php -->

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

[1]:

[To see links please register here]

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.

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

for two level، in Event model

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

> 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.

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.

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.