Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

php - Laravel query builder General error 2031

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...