I have been stuck since yesterday on a request that I can not solve. I make a site to manage an abandoned dog shelter. Dogs must be vaccinated:
- A first time in their life then a month later for a reminder
- Then once a year
So I am looking for from my table "animals" and my table "soinsveto" to list all dogs that have not had vaccines for over a year or if it is their first vaccine since more a month.
The goal is to put an alert in the administration interface that will say "attention this dog is not vaccinated, it should have been before the ...)
my table "animals"
id | name |
My table "soinveto" (typesoin_id = 1 if it is the first vaccine and there is a delay of one month before the next vaccine and 2 if the vaccine has a period of one year
id | typesoin_id | animal_id | datedusoin
Animal model
public function soinsvetos() {
return $this->hasMany(Soinsveto::class, 'animal_id');
}
soinveto model
public function animal()
{
return $this->belongsTo(Animal::class, 'animal_id')->withTrashed();
}
I am looking to select:
all the animals ("table animals") with the single last vaccination ("soinsvetos" table with typesoin_id = 1 or 2) by date ("datedusoin" field). I prefer to do with the dates because it is possible that the users seize vaccines in the disorder. Work on the "id" risk of Do not walk.
Then if "typesoin_id" is 1 I check that there is not more than one month with the date of today and if it is 2 that there is not more than one year.
I found a solution that works but is not optimized at all as you can see :
public function compose(View $view)
{
foreach (\App\Animal::get()
->all() as $animal) {
$soins = \App\Soinsveto::with('typesoin')
->where('animal_id', $animal->id)
->where(function ($q) { /// 1 = primovaccin et 2 = vaccins annuels
$q->where('typesoin_id', '=', '2')
->orWhere('typesoin_id', '=', '1');
})
->orderBy('datedusoin', 'desc')
->get()
->first();
if ($soins) {
if ($soins->typesoin_id == '1' && $soins->datedusoin < Carbon::now()->subMonths(1)) {
$crudFieldValue = Carbon::parse($soins->getOriginal('datedusoin'))->addyear(1)->toDateString();
}
if ($soins->typesoin_id == '2' && $soins->datedusoin < Carbon::now()->subYears(1)) {
$crudFieldValue = Carbon::parse($soins->getOriginal('datedusoin'))->addyear(1)->toDateString();
}
}
$notif[] = [
'title' => $soins->typesoin->nomsoin,
'start' => $crudFieldValue,
'nomanimal' => $animal->nom,
];
}
$view->with('notif', $notif);
}
With my version all the animals are loaded which makes a huge number of sql requests. I can not find any solutions to make this happen. I tried a lot of things but I can not get everything together in a single optimized query