Virtual rows on a query

HI im struggling to find a solution , I run a query that fetching shifts that has $shift->start_time and $shift->end_time when that fields are not null then we have a real shift sort by date like this screenshot: https://prnt.sc/u7z2cTgxHYRb

As you can see in that rows is the field start_time you see the missing dates like 1/03/2023, 2/03/2023 , 3/03/2023 So want to insert that missing dates as virtual rows , to define that missing dates as non working dates,

so is there any whey in query below to insert that virtual rows without make any insertion on SQL?

In other words want to to define when exist dates with start_time and end_time want a field like type=Working date, and when doesnt exist start_time and end_time create that mising start_time (date) and type = Not working

Query:

 $shifts = Shift::whereHas('post', function ($q) use ($association, $company, $annex, $shift_type, $city, $project) {
            $q->whereHas('project', function ($q) use ($association, $company, $annex, $city, $project) {

                if ($company != 'all') {

                    $q->whereHas('assignedCompanies', function ($q) use ($company) {
                        $q->where('company_id', '=', $company);
                    });

                }

                if ($annex != 'all') {

                    $q->whereHas('assignedAnnexes', function ($q) use ($annex) {
                        $q->where('annex_id', '=', $annex);
                    });
                }

                if ($city != 'all') {

                    $q->whereHas('cities', function ($q) use ($city) {
                        $q->whereIn('city_id', $city);
                    });
                }

                if ($project != 'all') {

                    $q->whereIn('id', $project);

                }
            });

    
        })->whereHas('workers', function ($q) use ($company, $worker) {
            $q->select('workers.id', 'workers.person_id')->with(['person' => function($k) {
                $k->select('phone');
            }]);
            if ($company != 'all') {

                $q->whereHas('companies', function ($q) use ($company) {
                    $q->where('company_id', '=', $company);
                });

            }

            if ($worker != 'all') {

                $q->whereIn('worker_id', $worker);

            }
        })->whereRaw('date(start_time) >= "'.$start_date.'" AND date(start_time) <= "'.$end_date.'"')
            ->select('shifts.*', DB::raw('DATE_FORMAT(start_time, "%Y-%m-%d") as start_date'))
            ->orderBy('start_time', 'ASC')->get();

LaryAI

3 years ago

Level 58

One solution to this problem is to use a combination of PHP and SQL to generate the virtual rows. Here's an example of how you could modify the existing query to achieve this:

// Define the start and end dates
$start_date = '2023-03-01';
$end_date = '2023-03-03';

// Fetch the real shifts
$shifts = Shift::whereHas('post', function ($q) use ($association, $company, $annex, $shift_type, $city, $project) {
    // ...
})
->whereHas('workers', function ($q) use ($company, $worker) {
    // ...
})
->whereRaw('date(start_time) >= "'.$start_date.'" AND date(start_time) <= "'.$end_date.'"')
->select('shifts.*', DB::raw('DATE_FORMAT(start_time, "%Y-%m-%d") as start_date'))
->orderBy('start_time', 'ASC')
->get();

// Generate the virtual rows
$dates = [];
$current_date = $start_date;
while ($current_date <= $end_date) {
    $dates[] = $current_date;
    $current_date = date('Y-m-d', strtotime($current_date . ' +1 day'));
}

$virtual_rows = [];
foreach ($dates as $date) {
    $found = false;
    foreach ($shifts as $shift) {
        if ($shift->start_date == $date) {
            $virtual_rows[] = $shift;
            $found = true;
            break;
        }
    }
    if (!$found) {
        $virtual_rows[] = (object) [
            'start_time' => $date . ' 00:00:00',
            'end_time' => $date . ' 00:00:00',
            'type' => 'Not working',
            'start_date' => $date,
        ];
    }
}

// Merge the real shifts and virtual rows
$shifts = collect($virtual_rows)->merge($shifts)->sortBy('start_time')->values();

This code first fetches the real shifts using the existing query. It then generates an array of dates between the start and end dates using a while loop. For each date, it checks if there is a real shift with that start date. If there is, it adds the shift to the $virtual_rows array. If there isn't, it creates a new object with the start and end times set to midnight and the type set to "Not working", and adds it to the $virtual_rows array.

Finally, the code merges the real shifts and virtual rows using the merge method of the collect helper, sorts them by start time, and re-indexes the array using the values method.

Note that this code assumes that the Shift model has a start_time, end_time, and type attribute, and that the start_time attribute is a datetime field. You may need to adjust the code to match your specific database schema.

webrobert

3 years ago

Level 51

Look at CarbonPeriod

$period = collect(CarbonPeriod::create($start_date, $end_date));

// then map that into your query result..

Please or to participate in this conversation.