John Davidson

Message:

I have an animal Model. Animal relations Examinations and Diseases models. I need filter to animals by which is sick.

Examination model has disease_id. If last examination object disease_id not equal to null this animal is sick. I am trying to filter this with spatie filter.

Here is my custom filter code:

This have to filter by last examination data. But filtering by all examination datas.

<?php



use Spatie\QueryBuilder\Filters\Filter;

use Illuminate\Database\Eloquent\Builder;



class SickAnimalsFilter implements Filter

{

    public function __invoke(Builder $query, $value, string $property)

    {

            $query->whereHas('examinations', function($q) {

                $q->orderBy('created_at', 'DESC')->limit(1)->where('disease_id', '!=', null);

            });

    }

}

Example data:

This should be include to filter. This animal is sick now.

"examinations": [

            {

                "id": 1,

                "animal_id": 1,

                "disease_id": 1

                "created_at": "2022-01-28T09:24:46.000000Z",

                "updated_at": "2022-01-28T09:24:46.000000Z",

            }

        ]

This should be not include to filter. This animal sick before but now it is healty.

"examinations": [

            {

                "id": 1,

                "animal_id": 1,

                "disease_id": 1

                "created_at": "2022-01-28T09:24:46.000000Z",

                "updated_at": "2022-01-28T09:24:46.000000Z",

            },

            {

                "id": 2,

                "animal_id": 1,

                "disease_id": null

                "created_at": "2022-01-30T15:29:00.000000Z",

                "updated_at": "2022-01-30T15:29:00.000000Z",

            }

        ]

---

My Answer:
You can achieve this by using a custom filter with Spatie's query builder package. Here's an example of how you can implement this custom filter:

php
use Spatie\QueryBuilder\Filters\Filter;
use Illuminate\Database\Eloquent\Builder;

class LastExaminationFilter implements Filter
{
public function __invoke(Builder $query, $value, string $property): Builder
{
return $query->whereHas('examinations', function ($query) {
$query->whereNotNull('disease_id')
->where('id', function ($subQuery) {
$subQuery->from('examinations')
->selectRaw('MAX(id)')
->whereColumn('animal_id', 'animals.id');
});
});
}
}


Then, you can use this custom filter in your controller like this:

php
use App\Models\Animal;
use Spatie\QueryBuilder\QueryBuilder;

$animals = QueryBuilder::for(Animal::class)
->allowedFilters('name', 'age', 'species')
->addFilter('is_sick', new LastExaminationFilter())
->get();


This will filter the animals based on the last examination data where the disease_id is not null, indicating that the animal is sick.