所以我在下面定义了几个关系表
父数据库：
public function backorderQuantities(){
return $this->hasMany(BackorderQuantity::class, 'ITEMNMBR', 'ITEMNMBR')->where('SOPTYPE', 5);
}
关系数据库：
public function item(){
return $this->belongsTo(Item::class, 'ITEMNMBR', 'ITEMNMBR');
}
我这样称呼这种关系：
Item::has('backorderQuantities')
->select('ITEMNMBR',
Item::raw("SUM(QTYONHND) as qty"),
Item::raw("SUM(QTYONORD) as ordered"),
Item::raw("SUM( ( CASE WHEN LOCNCODE LIKE 'IT-%' THEN QTYONHND END ) ) as transit"),
)
->where('PRIMVNDR', Auth::user()->vendor_id)
->groupBy('ITEMNMBR')
->orderBy($group['field'], $group['sort'])
);
这工作正常，但我还需要从 backorderQuantities 中选择字段，以便我可以按所述字段进行排序。换句话说，我想做这样的事情：
Item::has('backorderQuantities')
->select('ITEMNMBR',
Item::raw("SUM(QTYONHND) as qty"),
Item::raw("SUM(QTYONORD) as ordered"),
Item::raw("SUM( ( CASE WHEN LOCNCODE LIKE 'IT-%' THEN QTYONHND END ) ) as transit"),
'backorderQuantities->QUANTITY'
)
->where('PRIMVNDR', Auth::user()->vendor_id)
->groupBy('ITEMNMBR')
->orderBy('backorderQuantities->QUANTITY', $group['sort'])
但我遇到了这样的错误：
SQLSTATE[42S22]：[Microsoft][SQL Server 的 ODBC 驱动程序 17][SQL Server]列名称“backorderQuantities”无效。(SQL: select count(*) 作为聚合 from (select [ITEMNMBR], SUM(QTYONHND) 作为数量, SUM(QTYONORD) 作为订购, SUM( ( CASE WHEN LOCNCODE LIKE 'IT-%' THEN QTYONHND END ) ) 作为中转, json_value([backorderQuantities], '$."QUANTITY"') from [IV00102] 存在（从 [SOP10200] 选择 *，其中 [IV00102].[ITEMNMBR] = [SOP10200].[ITEMNMBR] 且 [SOPTYPE] = 5 ) 和 [PRIMVNDR] = YHI 按 [ITEMNMBR]) 分组为 [aggregate_table])
可能是因为当时该表仍在急切加载。有什么办法可以做到这一点吗？任何帮助将非常感激！
米脂
倚天杖