how to optimize the query, and not to hit the serve each time

i want to check the scheduling of a staff member for whole month. currently i m iterating through each date of a month, and then each hours in that specific day. and when i saw the debugger i got 1000+ queries. is there any optimized way to fetch all the scheduling from the staff table, stored in a variable and then check my condition through that array rather than hitting the server each time and getting data from db

#iterating each day for whole month
        foreach ($dates as $key => $today) {
            $daily = []; $daily[0] = 'fullyAvailable';
            $i = 1;

            #iterating each hours, 17 hours a day (17 iteration)
            for ($time = $opening_time; $time <= $closing_time; $time++) {
                $unit = $inst->instructor->hourlySubscribedUnit($today, date("H:00:00", mktime($time)), date("H:00:00", mktime($time + 1)));

                if (isset($unit)) {

                    $daily[0] = 'fullyScheduled';
                    $daily[$i] = $unit->unit_type;
                    $totalScheduledCount++;

                }elseif ($inst->instructor->timings($today)->count() > 0) {
                    $daily[0] = 'fullyAbsent';
                    break;

                } else {
                    $daily[$i] = 'available';
                    $totalAvailableCount++;
                }
                $i++;
            }
            $monthly[$key] = $daily;
        }

and this is the relationship defined inside the staff model

   {
      return $this->hasMany(SubscriptionUnits::class, 'instructor_id')
      ->whereDate('date', $date)
      ->when($from && $to, function($query) use ($from, $to){
         return $query->whereTime('from', '>=', $from)->whereTime('to', '<=', $to);
      })->select('unit_type')->first();
   }`

jlrdw

3 years ago

Level 75

I don't know all of your data, but seems a basic query on a related schedule table should simple show when an employee say Joe is scheduled. Eager load the data. I don't understand why you are iterating like that.


Parent table

id 5     Joe

===========

Related table

Here eager load Joe's schedule

Or depending on how you have tables setup use a join. For all employees a groupby with an orderby

salmankhan2482

3 years ago

Level 1

@jlrdw respected sir, actually i have to check the scheduling of a specific staff in scheduling table for each hours and then pass that data to a payload, which i show on a calendar in the blade.

i have to check each of his slot between 6-7 am and 7-8 am and 9-10 am and so on till 20-21 pm. and then storing each of the result in the payload .

jlrdw

3 years ago

Level 75

@salmankhan2482 I meant eager load the specific staffs data, send to blade and loop over how you need to.

https://laravel.com/docs/9.x/eloquent-relationships#eager-loading

salmankhan2482

3 years ago

Level 1

@jlrdw i will use eager loading but how would i be able to check for timing like between 7 to 9 am till 20 to 21 pm for each day of the whole month.

jlrdw

3 years ago

Level 75

@salmankhan2482 I don't know how you have it stored in the database. But it should be stored in a way you can work with the data easily. You said 1000+ queries, so also make sure you aren't having the n+1 query problem.

Here: https://laravel.com/docs/9.x/eloquent-relationships#eager-loading

Scroll down and find:

Eager loading alleviates the "N + 1" query problem

The documentation covers it there.

Please or to participate in this conversation.