0

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.

Xerakon
  • 159
  • 1
  • 16
  • there is a issue in your where() function. And also, I don't think what you are trying to achieve here is possible with eloquent. because your 'prescribe_date' column stores the 'full date' and you want to group them by 'month'. I suggest you should do this manually in php (not in mysql). – Tharaka Dilshan Sep 27 '18 at 16:40
  • @TharakaDilshan Could you provide a suggestion of how to approach this? I was hoping that using the date() function in the where() function to draw out the month from the full date, then match it against the current date/time's month. – Xerakon Sep 27 '18 at 17:19
  • Your `prescriptions` function is overkill. Also, avoid do do queries inside the view, do it inside the controller, or the model itself. – Elias Soares Sep 27 '18 at 18:10

4 Answers4

2

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)
Peyman
  • 312
  • 1
  • 8
  • From my understanding, that is how you narrow down the date down to a month, and then match it to a corresponding month. I have done it before with direct relationships, just not with one that goes through. another table. – Xerakon Sep 27 '18 at 16:40
  • @Xerakon What Payman is saying is that your syntax is invalid. You are passing a date, while the first parameter to `where` should be a column name. – Elias Soares Sep 27 '18 at 18:09
0

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 ?

Eduardo Junior
  • 362
  • 1
  • 10
  • The '9' is to simulate the current month. This code throws the error: Undefined property: Illuminate\Database\Eloquent\Relations\HasMany::$prescibe_date – Xerakon Sep 27 '18 at 17:08
  • It's not prescibe_date, it's prescribe_date, i'm sorry, typed wrongly. {{$doctor->prescriptions()->where('prescribe_date', date('m',strtotime($doctor->prescriptions()->prescribe_date)))->count()}} – Eduardo Junior Sep 27 '18 at 19:19
0

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();
Tharaka Dilshan
  • 4,371
  • 3
  • 14
  • 28
  • Thank you @tharaka. Using this, I pulled the current date using info from this answer on current date()s: https://stackoverflow.com/a/3321973/9990845 – Xerakon Sep 27 '18 at 18:33
0

Use this where clause.

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

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

Elias Soares
  • 9,884
  • 4
  • 29
  • 59