db::raw adding is null in query

Question

I am trying to use laravel query builder like:-

$users = DB::table('baid_collection')
         ->select(DB::raw('sum(total) as total_collect,collection_limit_c'))
         ->join('users_cstm', 'baid_collections.assigned_user_id', '=', 'users_cstm.id_c')
         ->where(DB::raw("assigned_user_id = '$userId' and DATE(date_entered)=CURDATE()"))
         ->groupBy('assigned_user_id')
         ->get();

This query should be like

select sum(total) as total_collect,collection_limit_c from `baid_collections` inner join `users_cstm` on `baid_collections`.`assigned_user_id` = `users_cstm`.`id_c` where assigned_user_id = '15426608-3ea5-f299-7a80-601bd06be2d9' and DATE(date_entered)=CURDATE() group by `assigned_user_id`

But last run query showing me

 select sum(total) as total_collect,collection_limit_c from `baid_collection` inner join `users_cstm` on `baid_collections`.`assigned_user_id` = `users_cstm`.`id_c` where assigned_user_id = '15426608-3ea5-f299-7a80-601bd06be2d9' and DATE(date_entered)=CURDATE() is null group by `assigned_user_id`

In query is null giving problem i dont want is null in query

instate of `CURDATE()` use `Carbon::today()` – Basharmal Mar 25 '21 at 06:54 — Basharmal, Mar 25 '21 at 06:54

score 3 · Accepted Answer · answered Mar 25 '21 at 07:38

Use whereRaw instead of where like

$users = DB::table('baid_collections')
         ->select(DB::raw('sum(total) as total_collect,collection_limit_c'))
         ->join('users_cstm', 'baid_collections.assigned_user_id', '=', 'users_cstm.id_c')
         ->whereRaw("assigned_user_id = '{$userId}' and DATE(date_entered)=CURDATE()")
         ->groupBy('assigned_user_id')
         ->get();

It's creating a problem due to where needs atleast two parameters and you are just passing one parameter

score 0 · Answer 2 · answered Mar 25 '21 at 06:54

Check this if it helps, write whereDate instead of add date in raw query. if you are not using Carbon, you can also use php date("Y-m-d"), method to get date

DB::table('baid_collection')
                ->select(DB::raw('sum(total) as total_collect,collection_limit_c'))
                ->join('users_cstm', 'baid_collections.assigned_user_id', '=', 'users_cstm.id_c')
                ->where(DB::raw("assigned_user_id = '$userId'"))
                ->whereDate("date_entered", "=", Carbon::now()->toDateString())
                ->groupBy('assigned_user_id')->get();

score 0 · Answer 3 · answered Mar 25 '21 at 07:01

You can try this

$users = DB::table('baid_collection')
         ->select(DB::raw('sum(total) as total_collect,collection_limit_c'))
         ->join('users_cstm', 'baid_collections.assigned_user_id', '=', 'users_cstm.id_c')
         ->where(DB::raw("assigned_user_id = '$userId' and DATE(date_entered) = '".date('Y-m-d')."'"))
         ->groupBy('assigned_user_id')
         ->get();

db::raw adding is null in query

3 Answers3