Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

awfarral's avatar

Complex query on a collection

I have an EmployeeController, and an AbsenceController When i load my index view showing all the absence records for an employee i use:

$absences = Employee::find($employee_id)->absence()->get();

and pass it to my page. In addition I need to query this set of data for records that are between dates based on the records having a start and end date field (absence->start-data , absence->end_date. I also need to restrict to where the Type is H (absence->type), and the only information that i need is the total duration of H in the period X to Y from the days fields of each record (absence->duration)

Is the best approach to query the $absences by using ->where between (is this possible) and can i handle the number of criteria that i have? I did think about trying to have a function in the Absence Modal but wasn't able to work that out, specifically how to call it from the AbsenceController, but also i wasn't sure what was the best approach, as i already have the collection of records

Hoping that i have explained myself enough !! Many Thanks

0 likes
4 replies
Tray2's avatar

I would say "ALWAYS" use the database to filter down the result set instead of looping through if later on.

There are of course some edge cases where you need to do some filtering on the php side.

You can attach any amount of where clauses to your query.

1 like
awfarral's avatar

Thank for the replies. I don't want to use a package as i want to ensure that i learn how to do it, rather than depend on a package.

I did manage achieve it with:

        $HolidayRecords = Employee::find($employee_id)->absence()
                                ->whereDate('startdate', '>=', '2020-01-01' )
                                ->whereDate('enddate', '<=', '2020-12-31' )
                                ->where('absence_code_id','2')
                                ->sum('duration');

dates wont be hard coded lol I wonder would a database function be better, and i can make re-usage and pass in variables, or would i be better have this is a function on employee?

Many Thanks Adam

Tray2's avatar

That depends. However in this case I would keep in in SQL and not push it into a database function. The reasons for that is that the parser will cache the query and make it faster the next run as well as functions are harder to trim and the parser may struggle with the best way to handle the query if the function is used in another query.

It doesn't really matter that much if the query is in the controller or in the model, it's a higly subjectiv preference.

Please or to participate in this conversation.