Multiple where conditions on eloquent model with optional where on relationship

Question

I have table training_schedules

• id (int, pk)
• course_reference (varchar)
• start_date (date)
• end_date (date)
• year_id (int, fk)

and years

• id (int, pk)
• value (int)

Now I want to create a search query using the TrainingSchedule eloquent model. I want to search by course_reference (using like) and start_date(optional) based on month (dropdownlist on form) and year (optional)

optional conditions means I will check if the form data for the fields are filled on the search form. If not I will just skip those conditions.

How can I achieve this using Eloquent ORM? if not, how to do it on another way?

Answer 1

You can create a query over multiple calls. So something like the following will probably do what you're looking for:

// start query off with a non-optional query
$results = TrainingSchedule::whereLike('course_reference', Input::get('course_reference'));

// only if we were sent a start date, add it to the query
if (Input::has('start_date')) {
    $results->whereStartDate(Input::get('start_date'));
}

// if we were sent a month, ensure start date's month is the same month as the one passed in
if (Input::has('month')) {
    $results->where(DB::raw('MONTH(`start_date`)'), Input::get('month'));
}

// only if we were sent a year, add it to the query
if (Input::has('year')) {
    $results->whereHas('year', function ($query) { $query->whereValue(Input::get('year')); });
}

 // get actual results from db
$results = $results->get();