mysql 2031_php – Laravel查询生成器常规错误2031

以下是使用Laravel查询构建器的查询：

$begin = new DateTime('2016-07-01');

$end = new DateTime('2016-07-31');

$startDate = $begin->format('Y-m-d 00:00:00');

$endDate = $end->format('Y-m-d 23:59:59');

$deposit = $depositModel->select(DB::raw('user_deposit.user_id as user_id,sum(user_deposit.amount) as total_deposit,null as total_withdraw'))

->whereBetween('date_time',[$startDate,$endDate])

->where('user_deposit.status',1)

->groupBy('user_deposit.user_id');

$withdraw = $withdrawModel->select(DB::raw('user_withdraw.user_id as user_id,null as total_deposit,sum(user_withdraw.amount) as total_withdraw'))

->whereBetween('user_withdraw.created_at',$endDate])

->where('user_withdraw.status',1)

->groupBy('user_withdraw.user_id');

$deposit = $deposit->unionAll($withdraw);

$transaction = DB::table(DB::raw("({$deposit->tosql()}) t"))

->select('user_id',DB::raw("sum(total_deposit) as total_deposit_amount,sum(total_withdraw) as total_withdraw_amount"))

->groupBy('user_id')

->get();

我希望得到如下结果：

"transaction": [

{

"user_id": 2,"total_deposit_amount": "101.00","total_withdraw_amount": "50.50"

},{

"user_id": 5,"total_deposit_amount": null,"total_withdraw_amount": "50.50"

}

]

但后来我不断得到sqlSTATE [HY000]：一般错误：2031.所以我在查询上使用了sql()来获取原始的SQL查询并试图在MysqL中运行它并且它产生了如上所述的预期结果.

下面是运行tosql()后的查询

SELECT`user_id`,SUM(total_deposit) AS total_deposit_amount,SUM(total_withdraw) AS total_withdraw_amount

FROM (( SELECT user_deposit.user_id AS user_id,SUM(user_deposit.amount) AS total_deposit,null AS total_withdraw

FROM `user_deposit`

WHERE`date_time` BETWEEN '2016-07-01' AND '2016-07-31'

AND `user_deposit`.`status` = 1

GROUP BY `user_deposit`.`user_id`)

UNION ALL (SELECT user_withdraw.user_id AS user_id,null AS total_deposit,SUM(user_withdraw.amount) AS total_withdraw

FROM `user_withdraw`

WHERE `user_withdraw`.`created_at` BETWEEN '2016-07-01' AND '2016-07-31'

AND `user_withdraw`.`status` = 1

GROUP BY `user_withdraw`.`user_id`)) t

GROUP BY `user_id`

所以问题是,我的查询构建器出了什么问题？为什么原始sql在查询构建器不工作时有效？

谢谢