CODEWITHSUNDEEP
phpmysqllaravel-4eloquent
Nithil George
Nithil George

Reputation: 275

Laravel 4.2: How to convert raw query to Eloquent

I have a raw query which works fine.

$qry ="select date(created_at) as Date,count(id) as Value from performances where date_format(created_at,'%d-%m-%Y') >= '$start_date' and date_format(created_at,'%d-%m-%Y') <= '$to_date' group by Date order by Date desc ";
    $stats = DB::select( DB::raw($qry) );
    return json_encode($stats);

I would like to convert it in to Eloquent

My controller function is

public function postPerformanceDetails()
{
$start_date = Input::get('start_date');
$to_date = Input::get('to_date');
$start_date = date('Y-m-d',strtotime($start_date));
$to_date = date('Y-m-d',strtotime($to_date));
$stats = Performance::where('created_at', '>=', $start_date)
        ->where('created_at','<=',$to_date)
        ->groupBy('perf_date')
        ->orderBy('perf_date', 'DESC')
        ->remember(60)
        ->get([
        DB::raw('Date(created_at) as perf_date'),
        DB::raw('COUNT(id) as perf_count')
        ])
        ->toJSON();
return $stats
}

The raw query works fine but eloquent does not work according to the date input.

I input data in this format 09-03-2015 in database the format is 2015-03-09

If we give 2015-03-09 as start_date and to_date it returns empty string.

Is there any problem with formats? How can i solve this issue?

Upvotes: 1

Views: 1121

Answers (2)

sleepless
sleepless

Reputation: 1789

The easiest way would be to convert the date in PHP to the database format.

$start_date = date('Y-m-d', strtotime($start_date));

This should lead to your database format: 2015-03-09.

Upvotes: 1

Nithil George
Nithil George

Reputation: 275

I got the answer as @sleepless suggested.

This is the code. 

public function postPerformanceDetails()
{
    $event = Input::get('events');
    $start_date = Input::get('start_date');
    $to_date = Input::get('to_date');
    $start_date = date('Y-m-d H:i:s',strtotime($start_date.'00:00:00'));
    $to_date = date('Y-m-d H:i:s',strtotime($to_date.'23:59:59'));
    $stats = Performance::where('created_at', '>=', $start_date)
    ->where('created_at','<=',$to_date)
    ->groupBy('perf_date')
    ->orderBy('perf_date', 'DESC')
    ->remember(60)
    ->get([
    DB::raw('Date(created_at) as perf_date'),
    DB::raw('COUNT(id) as perf_count')
    ])
    ->toJSON();
    return $stats;
}

Upvotes: 0

Related Questions