php - How to add where inside DB::raw() in laravel
I'm want to get the completed
orders amount, and the rejected
orders count in 1 query. Please see my code below. How can I add where or condition inside the DB::raw()?
return DB::table('restaurants')
->join('orders', 'restaurants.id', 'orders.restaurant_id')
->select([
DB::raw('SUM(orders.total) WHERE status = 8 as completed_sum'),
DB::raw('COUNT(DISTINCT(orders.transaction_code)) WHERE status = 3 as rejected_count')
])
->get();
Error
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;
Update
When I try ->toSql()
, I get this:
select `restaurant_name` as `restaurant`, SUM(total) WHERE status = 8 as completed_sum,
COUNT(DISTINCT(transaction_code)) WHERE status = 3 as rejected_count from `restuarants` inner join `orders` on
`restuarants`.`id` = `orders`.`restaurant_id` where `orders`.`date` between ? and ? group by `restuarants`.`id`
Answer
Solution:
id didn't got the chance to test it ... but it supposed to work
->select([
DB::raw('SUM(CASE WHEN status = 8 THEN orders.total END) completed_sum'),
DB::raw('COUNT(DISTINCT IF(status = 3,orders.transaction_code,null)) as rejected_count')
])
for sum with condition see:mysql sum
for count with distinct with if see this answer
Source