single query with multiple filter to multiple variables.

Hello All, I am trying fetch all tests which are scheduled today and tomorrow so i have query like below,

$datetime = new DateTime('tomorrow');
 $tomorrow = $datetime->format('Y-m-d'); 
$today = date('Y-m-d');

$todayTests = Test::join('courses','courses.course_id','=','tests.course')->where('tests.test_active',1)->where('tests.test_schedule_date',$today)->select('tests.*','courses.course_name','courses.course_id','courses.menu_name')->get();

$tomorrowTests = Test::join('courses','courses.course_id','=','tests.course')->where('tests.test_active',1)->where('tests.test_schedule_date',$tomorrow)->select('tests.*','courses.course_name','courses.course_id','courses.menu_name')->get();

is it possible to write single query and filter based on condition instead of writing two queries for each variable?

tykus

3 years ago

Level 104

Sure, there are a number of ways to achieve this, e.g.

$tests = Test::query()
    ->join('courses','courses.course_id','=','tests.course')
    ->where('tests.test_active',1)
    ->where(function ($builder) {
        $builder->where( 'tests.test_schedule_date', today())
            ->orWhere('tests.test_schedule_date', today()->addDay())
    })
    ->select('tests.*','courses.course_name','courses.course_id','courses.menu_name')
    ->get();

If needed, you can partition the resulting Collection based on the date if you include the date in the selected columns

Deekshith

3 years ago

Level 5

Thank you for the reply. But i want to pass to different variables. like today date results to $todayTests variable and tommorow date tests to $tommorowTests variable.

tykus

3 years ago

Level 104

@Deekshith that was the point of the partition operation - you would have a single query resulting in one Collection. If you include the test_schedule_date in the selected columns, you can separate the Collection like this:

[$todayTests, $tomorrowTests] = $tests->partition(fn ($test) => today()->eq($test-> test_schedule_date));

Please or to participate in this conversation.