php - Laravel query builder General error 2031

Question

Below is my query using Laravel query builder:

$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();

I was hoping to get the outcome like below:

"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"
            }
        ]

But then I keep getting SQLSTATE[HY000]: General error: 2031. So I used toSql() on the query to get the raw sql query and tried to run it in MySQL and it generated the expected result as above.

Below is the query after running 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`

So the question is, what's wrong with my query builder? Why does raw sql works while query builder doesn't?

Thanks

Answer 1

After much research, it seems like I have missed out this

mergeBindings($sub->getQuery())

My code:

$transaction = DB::table(DB::raw("({$deposit->toSql()}) t"))
                  ->mergeBindings($sub->getQuery())  // this is required for selecting from subqueries
                  ->select('user_id', DB::raw("sum(total_deposit) as total_deposit_amount, sum(total_withdraw) as total_withdraw_amount"))
                  ->groupBy('user_id')
                  ->get();