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;

bugsysha

5 years ago

Level 61

Maybe this will help https://reinink.ca/articles/calculating-totals-in-laravel-using-conditional-aggregates

Phread

5 years ago

Level 3

Here is an example of what I have done with db::raw. Note, I changed the table/field names to protect the guilty... :-)

I have used db::raw in maybe 2 queries out of ~50-60 queries. I HATE using them, it just makes the queries a bit more difficult to follow for others.

BTW, I thought I would add an example of getting a person's age, which was one of the driving reasons for me to use db::raw. I am sure there are other ways, but I knew this would do the job for me. Note, this will ONLY return the years, which is the only thing I needed to know - the Users have to be 18+ or I kick them out.

return DB::table('abcs')
            ->select('abcs.id as main_id',
                'abcs.theTitle as main_title',
                DB::raw('"IGNORE" as primary_body '),
                DB::raw('TIMESTAMPDIFF (YEAR, abcs.birthdate , CURDATE()) as main_age')
            )
            ->join('zref_toptypes', 'abcs.typeID', '=', 'zref_toptypes.id')
            ->where('abcs.userID','=', $userID)
            ->orderby("main_id","asc")
            ->get();

Please or to participate in this conversation.