CODEWITHSUNDEEP
phplaraveleloquent
Xerakon
Xerakon

Reputation: 159

Using the Date helper to Specify a Month or Laravel/Eloquent Issue?

I have done some searching around on this issue and continue to be stumped. I'm not sure if this is a date() issue, or a laravel/eloquent issue.

Background:

I have three tables. A 'doctor' table that has a belongsToMany relationship back and forth with a 'patients' table. Then a 'prescriptions' table that belongsTo a 'patient'. On the Doctor model, I have created a shortcut from the doctor table to the prescriptions with:

public function prescriptions() {
    $this->load(['patients.prescriptions' => function($query) use (&$relation) {
        $relation = $query;
    }]);

    return $relation;
}

What I am trying to achieve is to count the amount of times that a date - within the current month - within the 'prescribe_date" column of the prescriptions table shows up. So if a Doctor has sent in 10 prescriptions in August and 5 in September, it should show 5 (since we're currently in the month of September).

Here is what my Dashboard looks like:

$user = Auth::user();
$doctors = User::find(Auth::user()->id)->doctors()->orderBy('full_name', 'asc')->paginate(10);
return view('dashboard')->withDoctors($doctors);

And this is my dashboard.blade.php:

<table class="table table-hover table-sm">
  <thead>
    <tr>
      <th scope="col">Doctor Name</th>
      <th scope="col">Total Patients</th>
      <th scope="col">Monthly Prescriptions</th>
      <th scope="col">Date Doctor Added</th>
    </tr>
  </thead>
  <tbody>
    @foreach ($doctors as $doctor)
      <tr>
        <th scope="row">
          <a href="{{route('doctors.show', $doctor->id)}}">{{$doctor->full_name}}</a>
        </th>
        <td>{{$doctor->patients()->count()}}</td>

        // This is where I'm falling down
        <td>{{$doctor->prescriptions()->where(date('m', strtotime($doctor->prescriptions()->prescribe_date)), '9')->count()}}</td>

        <td>{{date('m-d-Y', strtotime($doctor->created_at))}}</td>
      </tr>
    @endforeach
  </tbody>
</table>

This is the error it is returning:

Undefined property:         
Illuminate\Database\Eloquent\Relations\HasMany::$prescribe_date

However, if I change this line to reflect a true date match, it returns the correct count:

{{$doctor->prescriptions()->where('prescribe_date', '1969-12-31')->count()}}

So I'm not sure if this is an issue with my php date() function, or an issue with my laravel/eloquent syntax, or something to do with database relationships. Thank you!

Update

I'm trying to use the date() function inside of the where function to draw out the current month from the 'prescribe' date column, then using the '9' as a simulation of the current actual month.

Upvotes: 0

Views: 1308

Answers (4)

Elias Soares
Elias Soares

Reputation: 10264

Use this where clause.

->whereRaw('MONTH(prescriptions.prescibe_date) = MONTH(CURDATE())');

It will do the comparison inside the database, and return what you want.

Upvotes: 0

Tharaka Dilshan
Tharaka Dilshan

Reputation: 4499

possible solution for your problem is,

Instead of get the month number, get the first day and last day of the month

// get first day and last day
$first_day = date('Y-m-d', strtotime('first day of August'));
$last_day = date('Y-m-d', strtotime('last day of August'));

$doctor->prescriptions()->whereBetween('prescribe_date', [$first_date, $last_date])->count();

Upvotes: 0

Eduardo Junior
Eduardo Junior

Reputation: 350

You should change the line : 

<td>doctor->prescriptions()->where(date('m',strtotime($doctor->prescriptions()->prescibe_date)), '9')->count()}}</td>

to

<td>{{$doctor->prescriptions()->where('prescibe_date', date('m',strtotime($doctor->prescriptions()->prescibe_date)))->count()}}</td>

That's because, the first argument passed to where() must be the table field name and the second must be the value you are searching for.

And what's the reason for that '9' on the where ? what were you trying to get ?

Upvotes: 0

Peyman
Peyman

Reputation: 312

You are using where function incorrectly. 

where(date('m', strtotime($doctor->prescriptions()->prescribe_date)), '9')

should be like:

where('column_name or key_name' , $value_that_youre_looking_for)

Upvotes: 2

Related Questions