我想使用MySQL在我的Laravel项目中创建一个逗号分隔的结果。我知道我必须使用GROUP_CONCAT,并且我使用工作台让它在mysql上完美地工作。但是当我在我的Laravel代码上使用它时,我遇到了一个错误。请检查我下面的实际代码。当我删除GROUP_CONCAT行/部分时,我的代码可以完美地工作。
调用接口时的错误码为:500(内部服务器错误)
$result = Rmim\WhQcHdr::from('wh_qc_hdrs as hdr')->select(
'rr.TrxDate as RR Date',
'hdr.rr_no as RR No',
'hdr.inspected_at as Date of Inspection',
'hdr.qcir_no as QCIR No',
'hdr.item_code as Item Code',
'item.ItemDesc as Item Description',
'hdr.supplier_code as Supplier Code',
'con.ContactName as Supplier Name',
'hdr.reject_reason as Reason of Rejection',
'hdr.cancel_reason as Reason of Cancellation',
\DB::raw('(CASE WHEN hdr.qc_status="A" THEN "Passed"
WHEN hdr.qc_status="CA" THEN "Conditionally Approved"
WHEN hdr.qc_status="R" THEN "Rejected"
WHEN hdr.qc_status="C" THEN "Cancelled"
ELSE "N/A"
END) as Disposition'),
-- line below causes an error when I call my api, it works perfectly if I remove it.
\DB::raw('GROUP_CONCAT(dtl.parameters) as Failed Parameters')
)
->leftJoin('rminv.tbl_rmtrninv as rr', function ($join){
$join->on('hdr.rr_no', '=','rr.trxnum')
->where('rr.TransID', '=', 2);
})
->leftJoin('rminv.tbl_rmmstitem as item', 'hdr.item_code', '=', 'item.ItemCode')
->leftJoin('fginv.tbl_fgmstcontacts as con','hdr.supplier_code','=','con.ContactCode')
->leftJoin('crp.wh_qc_dtls as dtl', function ($join){
$join->on('hdr.id', '=','dtl.qc_id')
->where('dtl.result', '=', 0);
})
->groupBy('rr.TrxDate',
'hdr.rr_no',
'hdr.inspected_at',
'hdr.qcir_no',
'hdr.item_code',
'item.ItemDesc',
'hdr.supplier_code',
'con.ContactName',
'hdr.reject_reason',
'hdr.cancel_reason',
\DB::raw('(CASE WHEN hdr.qc_status="A" THEN "Passed"
WHEN hdr.qc_status="CA" THEN "Conditionally Approved"
WHEN hdr.qc_status="R" THEN "Rejected"
WHEN hdr.qc_status="C" THEN "Cancelled"
ELSE "N/A"
END)'))
->orderBy('hdr.created_at', 'Desc');
提前谢谢你。
发布于 2019-03-11 13:54:28
我只需将Failed_Parameters的失败参数中的别名转换为1个单词,错误就消失了。我真的不知道为什么,因为在其他字段中,别名超过1个单词是可以的,但在GROUP_CONCAT部分得到一个错误。
如果有人能够解释这一点,请一定要解释,这将是一个很大的帮助。
https://stackoverflow.com/questions/55095147
复制相似问题