I have the following models in my laravel app:

Consumption:

class Consumption extends Model
{
    use HasFactory;

    protected $guarded = [];
    
    public $timestamps = false;

    public function ambulance() {
        return $this->belongsTo(Ambulance::class);
    }

    public function consumption_item() {
        return $this->hasMany( ConsumptionItem::class );
    }

    public function consumption_items_grouped() {
        return $this->consumption_item()
            ->select('consumption_items.id', 'consumption_items.consumption_id',
            'consumption_items.item_id', 'consumption_items.item_stock_id', ConsumptionItem::raw('SUM(consumption_items.quantity) as quantity'))
            ->groupBy('consumption_items.item_stock_id');
    }
}

ConsumptionItem:

class ConsumptionItem extends Model
{
    use HasFactory;

    protected $guarded = [];
    
    public $timestamps = false;

    public function consumption() {
        return $this->belongsTo( Consumption::class );
    }
}

As you can notice, the relationship between them is one-to-many, a consumption can have many items. Also, a consumption has an ambulance_id

Now, I have the following case: consumption 1 has ambulance_id = 5 and 10 items, consumption 5 has ambulance_id = 5 and 6 items

If two or more consumptions have the same ambulance_id, then it should group into one and also group the items. is there any way to group the consumptions based on the ambulance_id? so instead of having 2 different positions (consumption 1 with 10 items and consumption 5 with 6 items), to have only one (consumption 1 with 16 items)

This is my current query:

$consumptions = Consumption::with('consumption_items_grouped',
'ambulance')
            ->whereIn('consumptions.ambulance_id', $subset)
            ->whereBetween('consumptions.document_date', [$old_from_date, $old_until_date])
            ->get();

A more clear example: So the tables are consumptions and consumption_items. In the consumptions table I have:

id      ambulance_id

1       5

2       5

In the consumption_items table I have:

id      consumption_id      item_id

1       1                   7

2       1                   9

3       1                   4

4       2                   8

So instead of having two consumptions (consumption 1 with 3 items and consumption 2 with 1 item) I want only 1 consumption (consumption 1 with 4 items), since they both have the same ambulance_id

CodePudding user response：

if you don't use relations , you query like bellow, please run this query... and say result is correct?

Consumption::query()
    ->selectRaw('
        consumption_items.id,
        consumption_items.consumption_id,
        consumption_items.item_id,
        consumption_items.item_stock_id,
        SUM(consumption_items.quantity) as quantity
    ')
    ->leftJoin('consumption_items', 'consumption_items.consumption_id', '=', 'consumptions.id')
    ->groupBy('consumption_items.consumption_id')
    ->get();

CodePudding user response：

Idk if i understand you well, but what i think that you want is a list of itemps per ambulance like this:

Ambulances

[
    {
        "id": 5, 
        "name": "Ambulance 5", 
        "consumption_items": [
            {
                "id": 1, 
                "consumption_id": 1,
                "item_id": 7,
                "item": {...}
            },
            {
                "id": 1, 
                "consumption_id": 1,
                "item_id": 9,
                "item": {...}
            },
            {
                "id": 1, 
                "consumption_id": 1,
                "item_id": 4,
                "item": {...}
            },
            {
                "id": 1, 
                "consumption_id": 2,
                "item_id": 8,
                "item": {...}
            },
        ]
    }
]

For this you should create your ambulance class and add a hasmanythrough relation.

class Ambulance extends Model {
    public function consumption_items()
    {
        return $this->hasManyThrough(ConsumptionItem::class, Consumption::class, 'ambulance_id', 'consumption_id');
    }
}

And then you can do this to get te previous result (if you only need the number of items per ambulance replace the with "with" with "withCount" method).

$ambulances = Ambulance::with('consumption_items', 'consumption_items.item')->get(); 
// You can manipulate this result to return the count of consumptiom_itemps on each ambulance on a foreach loop like $ambulance->consumption_items->count();

There is no way tou group by consumption as if, cause if you think, you would have to determine randomly the consumption id (1 or 2 in your case) to show as you want.