-1

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

Vivek Modi
  • 31
  • 1
  • 9

3 Answers3

3

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

vivek modi
  • 800
  • 2
  • 7
  • 23
0

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();
Teekay
  • 114
  • 5
0

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();
Basharmal
  • 1,313
  • 10
  • 30