组laravel雄辩关系项

Question

我有以下模型在我的laravel应用程序:

消费:

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

您可以注意到，它们之间的关系是一对多的，一个消费可以有许多项。另外，一个消费有一个ambulance_id

现在，我有以下情况:消费1有ambulance_id = 5和10件物品，消费5有ambulance_id = 5和6项

如果两个或多个消费具有相同的ambulance_id，则应将其分组为一个，并将项目分组。是否有办法根据ambulance_id对消费进行分组?所以比起有2个不同的位置(消费1有10件物品和消费5有6件物品)，只有一个位置(消费1有16件物品)

这是我当前的查询:

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

一个更清晰的例子:这两个表分别是consumptions和consumption_items。在消费表中，我有:

id      ambulance_id
1       5
2       5

在consumption_items表中:

id      consumption_id      item_id
1       1                   7
2       1                   9
3       1                   4
4       2                   8

所以我只想要1次消费(消费1有3件物品和消费2有1件物品)，而不是有两个消费(消费1有4件物品)，因为它们都有相同的ambulance_id

Answer 1

我不知道我是否理解得很好，但我认为你想要的是每辆救护车的物品清单，就像这样:

救护车

[
{
"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": {...}
},
]
}
]

为此，您应该创建您的救护车类并添加一个hasmanythrough关系。

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

然后你可以这样做来得到之前的结果(如果你只需要每辆救护车的物品数量，用"代替"与"withCount"方法).

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

没有办法按消费分组，因为如果你认为，你必须随机确定消费id(在你的情况下是1或2)来显示你想要的。

Answer 2

如果您不使用关系，您的查询如下，请运行此查询…然后说结果是正确的?

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