我必須將兩個(gè)表連接在一起，經(jīng)過一番嘗試和錯(cuò)誤后，我能夠得到它，但是在嘗試對(duì)連接字段求和以便稍后按該數(shù)量排序時(shí)，我意識(shí)到數(shù)字比預(yù)期的要高得多。我嘗試遵循這個(gè)問題的解決方案，但它對(duì)我不起作用：The sum amount from join table is invalid。下面是我的查詢： $query = Item::has('backorders') ->join('SOP10200', 'IV00102.ITEMNMBR','=','SOP10200.ITEMNMBR') ->select('IV00102.ITEMNMBR', //These sums are wrong when using join Item::raw("SUM(IV00102.QTYONHND) as qty"), Item::raw("SUM(IV00102.QTYONORD) as ordered"), Item::raw("SUM( ( CASE WHEN IV00102.LOCNCODE LIKE 'IT-%' THEN IV00102.QTYONHND END ) ) as transit"), Item::raw("SUM(SOP10200.QUANTITY) as backorder"), ) ->where('IV00102.PRIMVNDR', Auth::user()->vendor_id) ->groupBy('IV00102.ITEMNMBR') ->orderBy($group['field'], $group['sort']) ->limit(2147483647);以下是我的人際關(guān)系：public function item(){ return $this->belongsTo(Item::class, 'ITEMNMBR', 'ITEMNMBR'); }public function backorders(){ return $this->hasMany(Backorder::class, 'ITEMNMBR', 'ITEMNMBR')->where('SOPTYPE', 5); }我想指出的是，我無法在聯(lián)接中使用關(guān)系標(biāo)識(shí)符，而必須直接引用表，但關(guān)系可以以其他方式工作，并且已經(jīng)在沒有聯(lián)接的情況下進(jìn)行了測(cè)試。換句話說，我加入的全部原因是為了我可以按缺貨訂單（SOP10200）進(jìn)行排序。如果有一個(gè)更優(yōu)雅的解決方案，我完全贊成。
1 回答
茅侃侃
TA貢獻(xiàn)1842條經(jīng)驗(yàn) 獲得超21個(gè)贊
我通過實(shí)現(xiàn)一個(gè)子查詢，對(duì)子查詢進(jìn)行求和，然后最終將其連接到主查詢來解決這個(gè)問題：
$subQuery = Backorder::select('ITEMNMBR', Backorder::raw('sum(QUANTITY) as backorder'), 'SOPTYPE')
->groupBy('ITEMNMBR', 'SOPTYPE');
$subQuerySql = $subQuery->toSql();
$query = Item::has('backorders')
->where('IV00102.PRIMVNDR', Auth::user()->vendor_id)
->leftjoin(Item::raw('(' . $subQuerySql . ') as bbo'),function($join) use ($subQuery) {
$join->on('IV00102.ITEMNMBR', '=', 'bbo.ITEMNMBR');
})
->where('bbo.SOPTYPE', 5)
->select('IV00102.ITEMNMBR',
'bbo.backorder',
Item::raw("SUM(IV00102.QTYONHND) as qty"),
Item::raw("SUM(IV00102.QTYONORD) as ordered"),
Item::raw("SUM( ( CASE WHEN IV00102.LOCNCODE LIKE 'IT-%' THEN IV00102.QTYONHND END ) ) as transit"),
)
->mergeBindings($subQuery->getQuery())
->groupBy('IV00102.ITEMNMBR', 'bbo.backorder')
->orderBy($group['field'], $group['sort'])
- 1 回答
- 0 關(guān)注
- 150 瀏覽
添加回答
舉報(bào)
0/150
提交
取消