How to join 3 tables with Laravel's Eloquent relationships with Eager Loading?

Question

So, an order has a foreign_key offer_id.

And an offer has a foreign_key item_id.

An item may be in multiple offers. But every offer has one item.

An offer may be in multiple orders. But every order has one offer.

When I do this:

$orders = Auth::user()->orders()
            ->with('offer')
            ->get();

I get this:

id: 3,
user_id: 1,
offer_id: 5,
created_at: "2019-02-15 00:40:31",
updated_at: "2019-02-15 00:40:31",
offer: {
    id: 5,
    item_id: 3,
    created_at: "2019-02-15 00:39:40",
    updated_at: "2019-02-15 00:39:40"
}

As you can see, I can get that for this offer the item_id: 3 But I want to get the whole item; all its columns, not just the id.

Normally, you would join these two tables. How to do this with Eloquent?

Here are my eloquent relationships:

Order

public function offer()
{
    return $this->belongsTo(Offer::class);
}

Offer

public function orders()
{
    return $this->hasMany(Order::class);
}

public function item()
{
    return $this->hasOne(Item::class);
}

Item

public function offers()
{
    return $this->belongsToMany(Offer::class);
}

Mihir Bhende · Answer 1 · 2019-02-15T01:35:25.107

1

If by food you mean items in an order, how about :

$orders = Auth::user()->orders()
            ->with('offer', 'offer.orders', 'offer.item')
            ->get();

edited Feb 15 '19 at 01:35

answered Feb 15 '19 at 01:14

Mihir Bhende

8,677
1
30
37

I get a `RelationNotFoundException Call to undefined relationship [items] on model [App\Order].` – padawanTony Feb 15 '19 at 01:17
Where is the items relation in your project, is it in order model ? – Mihir Bhende Feb 15 '19 at 01:24
Please see the updated description. I added the relationships. – padawanTony Feb 15 '19 at 01:32
Still not working: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'items.offer_id' in 'where clause' (SQL: select * from items` where items.offer_id in (2, 4, 5))` From what I see, it tries to look for an offer_id in items, which does not exist. It should be the other way around. So perhaps the relationships are wrong? – padawanTony Feb 15 '19 at 01:38
You have specified a relation in your offer model to is will expect item_id column. What’s the table structure like ? – Mihir Bhende Feb 15 '19 at 01:46
Yes. So, `orders` table has a foreign_key `offer_id`. And `offers` table has a foreign_key `item_id`. That's all. An item may be in multiple offers. But every offer has one item. An offer may be in multiple orders. But every order has one offer. – padawanTony Feb 15 '19 at 01:54
Solved it. The relationship between `item` and `offer` had to be reversed. Thanks for pointing me in the right way. – padawanTony Feb 15 '19 at 02:02

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

Laravel Eager Loading

Underneath this you will find nested eager loading:

To eager load nested relationships, you may use "dot" syntax. For example, let's eager load all of the book's authors and all of the author's personal contacts in one Eloquent statement:

$books = App\Book::with('author.contacts')->get();

In your case, we can get nested ones using the dot notation between relationships.

$orders = Auth::user()->orders()
            ->with(['offer', 'offer.item'])
            ->get();

edited Jun 20 '20 at 09:12

Community

1
1

answered Feb 15 '19 at 01:17

You mean `offer.items` not `offer.orders`, correct? It is still not working - `RelationNotFoundException Call to undefined relationship [items] on model [App\Offer].` – padawanTony Feb 15 '19 at 01:23
You said offer has orders, order have items. So it would be `offer.orders.items` to get items. On your order model, you have to define your relationship to the Item model. Which would be a `$this->hasMany()` it seems. If orders can have many items. – Feb 15 '19 at 01:24
No. An order has an offer and an offer has an item. Take a look at the foreign keys I have given in the first two lines of my description while I update it to include the relationships I have in my code. – padawanTony Feb 15 '19 at 01:27
My bad I read it wrong, so it would be `order.offers.items`. I've updated my answer above, that should work fine. As long as your Offer model has a relationship to items. – Feb 15 '19 at 01:28
@padawanTony It wouldn't be items, it would just be item since your relationship is HasOne. So please look at my updated answer. – Feb 15 '19 at 01:33
Still not working: `SQLSTATE[42S22]: Column not found: 1054 Unknown column 'items.offer_id' in 'where clause' (SQL: select * from `items` where `items`.`offer_id` in (2, 4, 5))` From what I see, it tries to look for an `offer_id` in `items`, which does not exist. It should be the other way around. So perhaps the relationships are wrong? – padawanTony Feb 15 '19 at 01:36

padawanTony · Accepted Answer · 2019-02-15T02:14:08.770

The answers from both @MihirBhende and @swonder pointed in the right way.

It should indeed be:

$orders = Auth::user()->orders()
            ->with('venue')
            ->with('offer.item')
            ->get();

or (same thing):

$orders = Auth::user()->orders()
            ->with(['venue', 'offer.food'])
            ->get();

But the relationship of the Offer and Item models should be reversed:

Offer

public function item()
{
    return $this->belongsTo(Item::class);
}

Item

public function offers()
{
    return $this->hasMany(Offer::class);
}

How to join 3 tables with Laravel's Eloquent relationships with Eager Loading?

3 Answers3