[FIXED] How to get days in Eloquent Query In Laravel

Issue

I want to find days between 2 dates in Eloquent Query In Laravel. I have Query Like this,

$credentials = Documents::whereIn('agency_id', $agency_ids)
    ->with('dp_credential')
    ->get()->toArray();

if (count($credentials)) {
    foreach ($credentials as $val) {
        if ($val['dp_credential']) {
            $current_date = new DateTime(date('Y-m-d'));
            $expiry_date = new DateTime($val['dp_credential']['expiry_date']);
            $interval = $current_date->diff($expiry_date);
            $days = $interval->format('%r%a'); // if you getting days in minus then use %r
            $val['dp_credential']['days'] = $days;
            $val['dp_credential']['is_expiry'] = 0;
            if ($days < 30) {
                $val['dp_credential']['is_expiry'] = 1;
            }
        }
    }
}

Eager Loading Relationship

public function dp_credential() {
    return $this->hasone('App\Model\ClinicianCredentials', 'document_type', 'id')
        ->where('clinician_id', Auth::User()->id);
}

Now I Want to Find days without for each loop in eloquent query between the current date and dp_credential exist one date. and if days less than 30 then I want to add one extra key, is it possible without for each loop.

This works fine but I want this one without for each loop.

Sorry for my Poor English.

Solution

You may call other query builder methods to further customize the eager loading operation:

Documents::whereIn('agency_id', $agency_ids)
           ->with(['dp_credential' => function ($query) {
                 $query->select(DB::raw('*, datediff(expiry_date, now()) as days, IF(datediff(expiry_date, now()) < 30, 1, 0) as is_expiry'));
           }])->get()->toArray();

Answered By - Ryan Nghiem