Конструктор запросов 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', [$startDate, $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, Так что я использовалtoSql () на запрос, чтобы получить сырой 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`
Итак, вопрос в том, что не так с моим конструктором запросов? Почему raw sql работает, а построитель запросов - нет?
Спасибо