Laravel Eloquent query relations with 3 levels

Question

I want some help of forming queries on Laravel Eloquent

I have 4 models in my application

Item Model

class Item extends Model
{
    use HasFactory;

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
       'code', 'name', 'type', 'price', 'cost', 'reorder_level', 'status'
    ];


    public function grnoteitems()
    {
        return $this->hasMany(Grnoteitem::class);
    }
}

Grnote Model

class Grnote extends Model
{
    use HasFactory;

    protected $fillable = [
        'date', 'number', 'warehouse_id','user_id', 'authorized_id', 'approved_id', 'notes'
    ];

    public function grnoteitems()
    {
        return $this->hasMany(Grnoteitem::class);
    }

    public function warehouse()
    {
        return $this->belongsTo(Warehouse::class);
    }
}

Grnoteitem Model

class Grnoteitem extends Model
{
    use HasFactory;

    protected $fillable = [
        'grnote_id', 'item_id', 'description', 'packing', 'quantity', 'price', 'total'
    ];

    public function grnote()
    {
        return $this->belongsTo(Grnote::class);
    }

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

Warehouse Model

class Warehouse extends Model
{
    use HasFactory;

    protected $fillable = ['name', 'address'];

    public function grnotes()
    {
        return $this->hasMany(Grnote::class);
    }
}

Quantity of the item is calculated by multiplying quantity and packing columns in the grnotes table.

Now I want to retrieve all the items with their quantity(quantity * packing) from a particular warehouse.

I tired the below query

$items = Item::withSum('grnoteitems',  'quantity', function($query) use($warehouse, $from, $to){

            $query->with('grnote', function($query1) use($warehouse, $from, $to){
                  $query1->where('warehouse_id', $warehouse->id)
                        ->whereBetween('date', [$from, $to])->get();
                    })->get();
            })->get();

This is working fine. But I don't find any way to get sum of two multiplied columns

I want something like

$items = Item::withSum('grnoteitems', '(quantity * packing)', function($query) use($warehouse, $from, $to){

            $query->with('grnote', function($query1) use($warehouse, $from, $to){
                  $query1->where('warehouse_id', $warehouse->id)
                        ->whereBetween('date', [$from, $to])->get();
                    })->get();
            })->get();

Please help me to solve this.

Answer 1

It's a bit long to write all the code to do this, but my idea would to :

• start your query from the wareHouse you want items from
• so Warehouse::first() [with grnote [with item]] and custom where stuff
• then the data you need to calculate are on the pivot table between Item and Grnote, so I would add withPivot to both relations
• in this query order, the pivot value will be appended as a relation to the Item object
• I would add a new attribute to the item model. Checking if the pivot attribute is set (so it comes from a query with pivot), calculate the quantity, and append it to the Item instance

You can also loop on the resulting Collection to append your new attribute.