"Transform" Query Data before mapping

[mxmtsk]

I have a case where I need to severely transform the result of the query before adding them to the sheet, cause I query data to make some calculations that should go into the excel - but the rows of the query don't match the rows that should be in the sheet.

I tried to create a custom collection, however my export times out cause the query in the collection method is not split like it would be, if I would use the query method and the dataset is quite large. However, if I would use the query method, I have no idea how I would be able to transform the data so that i can use it my way.

    public function collection()
    {
        $startDate = Carbon::parse(EventContext::getEvent()->start_date);
        $endDate = Carbon::parse(EventContext::getEvent()->end_date);

        EventContext::setEvent($this->eventId);
        $crewRoles = EventContext::getEvent()
            ->crewRoles()
            ->where('onsite', true)
            ->select(
                'id',
                'first_day_on_site',
                'last_day_on_site',
                'catering_dinner',
                'days_on_site',
                'catering_breakfast',
                'catering_lunch',
                'catering_overnight',
                'catering_kits'
            )->get();

        $days = collect([]);

        $currentDate = $startDate;
        while ($currentDate <= $endDate) {
            // Initialize counts for each flag for the current day
            $counts = [
                'day' => $currentDate->toDateString(),
                'catering_dinner' => 0,
                'catering_lunch' => 0,
                'catering_overnight' => 0,
                'catering_kits' => 0,
                'catering_breakfast' => 0,
            ];

            // Calculate counts for each flag based on CrewRoles
            foreach ($crewRoles as $crewRole) {
                if ($crewRole->days_on_site && in_array($currentDate->toDateString(), $crewRole->days_on_site)) {
                    if ($crewRole->catering_dinner) {
                        $counts['catering_dinner']++;
                    }
                    if ($crewRole->catering_lunch) {
                        $counts['catering_lunch']++;
                    }
                    if ($crewRole->catering_kits) {
                        $counts['catering_kits']++;
                    }
                    if ($crewRole->catering_overnight) {
                        $counts['catering_overnight']++;
                    }
                    if ($crewRole->catering_breakfast) {
                        $counts['catering_breakfast']++;
                    }
                }
            }

            $days->push($counts);

            $currentDate->addDay();
        }

        return $days;
    }

So basically I want to have a row in the sheet for every day of the event but based on the data from the query.

Is that out of scope for this library?