Eloquent hasMany with hasMany and a join in the middle

Question

I have this database structure

orders ====►  order_items  ====►  order_item_meta
  ║                |
  ║                |
  ▼                ▼
order_meta      products

The relations are orders hasMany order_items which hasManyThrough order_item_meta, orders also hasMany order_meta. In addition, the order_items/product_id needs to be joined with the products table.

I have the order_id and I am trying to get the whole data in one call. But I have a weird issue. This is the current code:

    $orders = Orders::
        with([
            'order_items' => function($q) {  //#1
                $q->leftJoin('products','order_items.product_id', '=', 'products.id');
            }
        ])
        ->with(['order_items.orderitem_meta'])  //#2
        ->with(['order_meta']);  //#3

It seems that with#1 and with#2 are interfering with each other.

Case1: If I do with#1+with#3, I am able to see in the result the data from the product table + the data from order_items, but not the data from order_item_meta.

Case2: If I do with#2+with#3, I am able to see in the result the data from the from order_items + data from order_item_meta, but not from the product table.

In both cases data from with#3 is ok.

But if I do all three together (with#1+with#2+with3) I get the same results as case1. data from order_item_meta is missing.

Orders.php

class Orders extends Model
{

    public function order_items()
    {
        return $this->hasMany('App\OrderItem','order_id','id'); //'foreign_key', 'local_key'
    }

    public function order_meta()
    {
        return $this->hasMany('App\OrderMeta','order_id','id'); //'foreign_key', 'local_key'
    }

    public function orderitem_meta()
    {
        return $this->hasManyThrough(
            'App\OrderItem',
            'App\OrderItemMeta',
            'order_item_id', // Foreign key on order_itemmeta table...
            'order_id', // Foreign key on order_item table...
            'id', // Local key on order_item table...
            'id' // Local key on order_itemmeta table...            
        ); 
    }    

}

OrderItem.php

class OrderItem extends Model
{

    public function order()
    {
        return $this->belongsTo('App\Orders');
    }

    public function orderitem_meta()
    {
        return $this->hasMany('App\OrderItemMeta','order_item_id','id'); //'foreign_key', 'local_key'
    }

}

OrderItemMeta.php

class OrderItemMeta extends Model
{
    protected $table = 'order_itemmeta';

    public function orderitem()
    {
        return $this->belongsTo('App\OrderItem');
    }
}

What is the correct way to do this query?

score 0 · Accepted Answer · answered Jul 05 '19 at 07:34

I solved it by adding a relationship between the order_items and the products:

in OrderItem.php

public function product()
{
    return $this->hasOne('App\Products','id','product_id'); //'foreign_key', 'local_key'
}

then the query becomes this:

$orders = Orders::
        with(['order_items.orderitem_meta','order_items.product','order_meta']);

and it works

Eloquent hasMany with hasMany and a join in the middle

1 Answers1