CODEWITHSUNDEEP
mysqllaravellaravel-query-builder
Krzysztof Michalski
Krzysztof Michalski

Reputation: 831

Laravel query Builder with DATE mysql function

Hi I have a query in my laravel project which should retrieve dates between ranges. My query is:

 $dateFrom = date("Y-m-d", strtotime($request->dateFrom));
      $dateTo = date("Y-m-d", strtotime($request->dateTo));
      $posts = DB::table('posts')->join('categories','posts.category_id','=','categories.id')->
                join('users','posts.user_id','=','users.id')
                ->select('posts.id','posts.topic','categories.category_name','posts.created_at','users.name')
                ->whereBetween('posts.created_at',[$dateFrom,$dateTo])
                ->get();

I'd like to use DATE mysql_function on column posts.created_at but I don't know how could I do it. Below query dosen't work:

$dateFrom = date("Y-m-d", strtotime($request->dateFrom));
      $dateTo = date("Y-m-d", strtotime($request->dateTo));
       $posts = DB::table('posts')->join('categories','posts.category_id','=','categories.id')->
                join('users','posts.user_id','=','users.id')
                ->select('posts.id','posts.topic','categories.category_name','posts.created_at','users.name')
                ->whereBetween('DATE(posts.created_at)',[$dateFrom,$dateTo])
                ->get();

I would be greatful for help. Best regards ;)

Upvotes: 2

Views: 3579

Answers (2)

DevK
DevK

Reputation: 9942

Try using Carbon like this:

use Carbon\Carbon;
...

$dateFrom = Carbon::parse($request->dateFrom);
$dateTo = Carbon::parse($request->dateTo);
$posts = DB::table('posts')
    ->join('categories','posts.category_id','=','categories.id')
    ->join('users','posts.user_id','=','users.id')
    ->select('posts.id','posts.topic','categories.category_name','posts.created_at','users.name')
    ->whereBetween('posts.created_at',[$dateFrom,$dateTo])
    ->get();

Upvotes: 0

wahdan
wahdan

Reputation: 1258

change ->whereBetween('DATE(posts.created_at)'

to ->whereBetween(DB::raw('DATE(posts.created_at)'))

if you want to add some raw expression in a query you can use DB::row method as mentioned in laravel documentation

Upvotes: 1

Related Questions