Laravel How to Extra Rows with Duplicates-CodePudding

I am having problem constructing my query to find rows containing duplicated values specifically in ('day','time_from',''time_to').

The problem is that the value of 'day' column could be 1 or 2 combination(M or M-W)

The query should extract the following ('M-W','7:00','8:30') and ('M','7:00','8:30') as duplicate.

Is there any workarounds with these? Quite new to Laravel.

This my current query which can only extract if ('day') are exact value.

$dupeTeacher = DB::table('load_schedule')
    ->select('time_from', 'time_to', 'day')
    ->groupBy('time_from', 'time_to', 'day')
    ->havingRaw('COUNT(*) > 1');

    $dupliTeacher=DB::table('load_schedule')
    ->select('load_schedule.*')
    ->joinSub($dupeTeacher, 'dupe_teacher', function ($join)
     {
        $join->on('load_schedule.day','=', 'dupe_Schedules.day');
        $join->on('load_schedule.c_time','=', 'dupe_Schedules.time_from');
        $join->on('load_schedule.c_time','=', 'dupe_Schedules.time_to');
    })
    ->paginate(10);

CodePudding user response：

I don't think you can do it as you're currently envisaging, as even if you could get it to see "M-W" as being a duplicate of either "M" or "W", it would not see it as a duplicate for "T". And two days of the week start with T...

You would be better, I think, storing the schedule entries in a separate table, using a relationship to tie it to a particular teacher. Each schedule entry would have a start time and an end time - when you populate it (however you populate it) if the schedule is for Monday 8:00 - 9:30 it just creates one entry in the schedule entries table. If the schedule is for Monday - Wednesday 8:00 - 9:30 then it creates three entries in the schedule entries table.

Ideally you would store these as datetime fields (ie. the actual date of the Monday / Tuesday / Wednesday in question, so a schedule entry for today would have a start time of 2021-12-08 08:00:00 and an end time of 2021-12 09:30:00 but if these are teachers, then it may be that it is "every Monday at 08:00:00" in which case your schedule entries table would have one column for the day of the week (as an integer, so 1 for Monday, 2 for Tuesday, etc.), one column for start time and one column for end time.

As it stands, you're going to be doing a lot of juggling to get it to work as you envisage - the above approach would simplify it considerably.