Query efficiency -> merge 2 queries with a join or union-CodePudding

I need some serious help/direction. I have two tables:

students 
-id
-site_id
-name
-enter_date
-exit_date

student_meals
-id
-site_id
-student_id
-meal_type_id (1, 2, or 3)
-date_served

I need two arrays:

All students enrolled on the requested 'serviceDate' ('serviceDate is between their enter_date and exit_date) that DO NOT have a meal_type_id of the requested mealType on the rquested serviceDate.

All students enrolled on the requested 'serviceDate' ('serviceDate is between their enter_date and exit_date) that DO have a meal_type_id of the requested mealType on the requested serviceDate.

I got it to work with the following:

 'unservedStudents' => Auth::user()->site 
                ->students()
                ->where('enter_date', '<=',Request::only( 'serviceDate') )
                ->where('exit_date', '>=',Request::only( 'serviceDate') )
                ->OrderByName()
                ->filter(Request::only('search', 'serviceDate', 'mealType'))
                ->get()
                ->map(fn ($students) => [
                    'id' => $students->id,
                    'name' => $students->name,
                ]),    
                
          'servedStudents' => Auth::user()->site
                ->student_meals()
                ->with('student')
                ->where('meal_type_id', Request::only( 'mealType'))
                ->where('date_served', Request::only( 'serviceDate'))
                ->orderBy('created_at', 'DESC')
                ->get()
                ->map(fn ($served_students) => [
                    'id' => $served_students->id,
                    'student' => $served_students->student ? $served_students->student->only('id','name') : null,
                ]),

//Filter for students

 public function scopeFilter($query, array $filters)
    {
        $mealType = $filters['mealType'] ?? null;
        $serviceDate = $filters['serviceDate'] ?? null;
        $search = $filters['search'] ?? null;

$query
            ->when($search, function ($query) use ($search) { 
                $query->where( fn ($query) =>
                    $query->where('first_name', 'like', '%'.$search.'%')
                   
        })
            ->when( $mealType, function ($query) use ($mealType, $serviceDate) {
                $query->whereDoesntHave('student_meals', fn ($query) => 
                    $query->where('meal_type_id', $mealType )
                        ->where('date_served', $serviceDate));
        });

When I seeded my database ites that have more than 400 students or so gets really slow. I'm pretty sure I need to condense the two queries above, but I can't figure out the logic.

Below is an attempt, but it gives me an error 'Method Illuminate\Database\Eloquent\Collection::getBindings does not exist'.

$students  = Auth::user()->site 
                ->students()
                ->join('student_meals as m', 'm.student_id', '=', 'students.id')//this is my attempt to get the same columns as the table to union....
                ->where('enter_date', '<=',Request::only( 'serviceDate') )
                ->where('exit_date', '>=',Request::only( 'serviceDate') )
                ->where('date_served', '=',Request::only( 'serviceDate') )
                ->filter(Request::only('search', 'serviceDate', 'grade', 'hr'))
                ->select('students.id as studentId', 'first_name',  'students.site_id as siteId',  'm.id as mealId', 'm.meal_type_id', )
                ->get()
                ->map(fn ($students) => [
                    'id' => $students->studentId,
                    'name' => $students->first_name,
                    'siteId' => $students->site_id,
                    'mealId' => $students->mealId,
                    'mealType' => $students->meal_type_id,
                ]),    


            'student_meals' => Auth::user()->site 
                ->student_meals()
                ->join('students as s', 's.id', '=', 'student_meals.student_id')
                ->where('date_served', '>=',Request::only( 'serviceDate') )
                ->where('meal_type_id', '>=',Request::only( 'mealType') )
                ->select('s.id as studentId', 'first_name',  
                's.site_id as siteId',  'student_meals.id as mealId', 'meal_type_id')
                ->union($students)
                ->map(fn ($students) => [
                    'id' => $students->studentId,
                    'name' => $students->first_name,
                    'siteId' => $students->site_id,
                    'mealId' => $students->mealId,
                    'mealType' => $students->meal_type_id,
                ]),

If you're up for it, I'd really appreciate any insight/help/pointers/tips.

CodePudding user response：

I think that your problem is very simple if you use the collections

//Relation name should be meals instead of student_meals because is redundant that a student has many student meals

$students = Student::with([
    'meals' => function ($query) use ($request) {
        $query->where('date_served', $request['serviceDate']);
    }
])
    ->where('site_id', $request->user()->site_id)
    ->where('enter_date', '<=', $request['serviceDate'])
    ->where('exit_date', '>=', $request['serviceDate'])
    ->get();

At this point you have all students that has the requested serviceDate between enter_date and exit_date and belongs to the same site_id of the current user (lazy loading all the meals of the student that belongs to the requested serviceDate), so, all you have to do is spread them in two different collections.

//Students with requested meal type
$swrmt = collect();
//Students without requested meal type
$swtrmt = collect();

foreach ($students as $student) {
    //If student contains at least one meal with the requested mealType
    if ($student->contains('meals.meal_type_id', $request['mealType'])) {
        $swrmt->push($student);
    } ese {
        $swtrmt->push($student);
    }
}

So you only have one query, and only need to be worried if the result is greater than 2000 students, if that happens would be necesary to change the with for a load using chunk of 2000 for preventing limit param query error. (Sorry if there is any type mistake, i write all of this on my cellphone), and don't forget to add your name filter at the main query with the same when that you alredy use.