Filters problem eloquent model query
Hello guys
i have a frontend form with dropdown and fulltext search, if user don't set any filters the index method of controller return all data otherwise return data that match with filters. The filters are passed directly in url using "GET"
I get data using model scope from mysql database and at the moment i merge all query using unionAll but the filters stop working I can't append the "->get()" method in each query because after this i need to paginate all results set and filter dynamically based on inputs from frontend that can includes some id of relationship
I must order each result's set and this is the reason why i have 4 different variables. Is there any method or single query can help me reach this ?
EDIT: after a few test i think the filters are applied only on the first table but i can't understand why
TeacherCourseService.php - service
public function getCourses($params, $elementsPerPage = 10)
{
// Get ordered teacher courses using model scope
$importantCourses = TeacherCourse::with(['schoolClasses', 'disciplinarySectors'])->IsImportant();
$notImportantCourses = TeacherCourse::with(['schoolClasses', 'disciplinarySectors'])->IsNotImportant();
$lastCourses = TeacherCourse::with(['schoolClasses', 'disciplinarySectors'])->IsLast();
$pastCourses = TeacherCourse::with(['schoolClasses', 'disciplinarySectors'])->IsPast();
// Here I need to merge all 4 query
$courses = $importantCourses->unionAll($notImportantCourses)->unionAll($lastCourses)->unionAll($pastCourses);
// Filtering data if filters exist
$this->applyFilters($params['online'] ?? null,$params['school-classes'] ?? null,$params['disciplinary-sectors'] ?? null,
$params['types'] ?? null, $params['locations'] ?? null, $params['is-visible'] ?? null, $params['title'] ?? null,
$params['state'] ?? null, $courses, $params['text'] ?? null
);
$params['location'] = $params['locations'][0] ?? null;
return $courses->paginate($elementsPerPage)->appends($params);
}
public function applyFilters($online, $schoolClasses, $disciplinarySectors, $types, $locations, $isVisible, $title, $state, $courses, $text)
{
//controllo filtro per classe scuola (es. primaria, superiori, etc..)
if (! empty($schoolClasses) && ! is_null($schoolClasses[0])) {
$courses->whereHas(
'schoolClasses', function ($query) use ($schoolClasses) {
$query->whereIn('id', $schoolClasses);
});
}
//controllo filtro settore disciplinare
if (! empty($disciplinarySectors) && ! is_null($disciplinarySectors[0])) {
$courses->whereHas(
'disciplinarySectors', function ($query) use ($disciplinarySectors) {
$query->whereIn('id', $disciplinarySectors);
});
}
//controllo filtro tipologia
if (! empty($types) && ! is_null($types[0])) {
$courses->whereHas(
'types', function ($query) use ($types) {
$query->whereIn('id', $types);
});
}
//controllo filtro sede
if (! empty($locations) && ! is_null($locations[0])) {
$courses->whereHas(
'location', function ($query) use ($locations) {
$query->whereIn('id', $locations);
});
}
//controllo filtro testo
if (!empty($text)) {
$courses->where(function($q) use ($text) {
$q ->where('title', 'like', '%'.$text.'%')
->orWhere('short_description', 'like', '%'.$text.'%')
->orWhere('description', 'like', '%'.$text.'%')
->orWhere('teachers', 'like', '%'.$text.'%');
});
}
if (isset($isVisible)) {
$courses->where('is_visible', $isVisible);
}
if (isset($online)) {
$courses->where('online', $online);
}
if (isset($title)) {
$courses->where('title', 'LIKE', '%'.$title.'%');
}
if (isset($state)) {
$courses->where('state', $state);
}
// if (! empty($orderBy)) {
// $courses->orderBy($orderBy);
// } else {
// //ordino per il primo appuntamento
// $courses->join(DB::raw('
// (
// SELECT
// cd.`t_course_id`,
// MIN(cd.`date`) as first_date
// FROM
// `t_course_dates` cd
//
// GROUP BY
// cd.`t_course_id`
// ) A
//
// '), 't_courses.id', '=', 'A.t_course_id');
//
// $courses->orderBY('is_important', 'DESC')->orderBy('closing_date', 'DESC');
// }
}
TeacherCourseController.php - controller
public function index(Request $request)
{
$params = [];
$params['school-classes'] = $request->input('school-classes');
$params['disciplinary-sectors'] = $request->input('disciplinary-sectors');
$params['types'] = $request->input('types');
$params['locations'] = $request->input('locations');
$params['online'] = $request->input('online');
$params['is-visible'] = true;
$params['text'] = $request->input('text');
// dd($request->all());
$courses = $this->teacherCourseService->getCourses($params);
$schoolClasses = TeacherSchoolClass::all();
$disciplinarySectors = TeacherCourseDisciplinarySector::all();
$types = TeacherCourseType::all();
$locations = TeacherCourseLocation::whereHas(
'courses', function ($q) {
$q->where('is_visible', 1);
}
)->get();
return view('frontend.teacherCourses.index', [
'schoolClasses'=> $schoolClasses,
'disciplinarySectors'=> $disciplinarySectors,
'types'=> $types,
'locations'=> $locations,
'courses'=> $courses,
]);
}
TeacherCourse.php - model
public function scopeIsImportant($query)
{
$today = now()->format('Y-m-d');
return $query->where([
['is_important', '=', 1],
['closing_date', '>', $today],
['move_last', '=', 0]
])->orderBy('closing_date', 'ASC');
}
public function scopeIsNotImportant($query)
{
$today = now()->format('Y-m-d');
return $query->where(function ($q) use ($today) {
$q->where([
['closing_date', '>', $today],
])->where([
['is_important', '=', 0],
['move_last', '=', 0]
])->orWhere([
['is_important', '=', 1],
['move_last', '=', 1]
]);
})->orderBy('closing_date', 'ASC');
}
public function scopeIsLast($query)
{
$today = now()->format('Y-m-d');
return $query->where([
['is_important', '=', 0],
['closing_date', '>', $today],
['move_last', '=', 1]
])->orderBy('closing_date', 'ASC');
}
public function scopeIsPast($query)
{
$today = now()->format('Y-m-d');
return $query->where([
['closing_date', '<', $today],
])->orderBy('closing_date', 'DESC');
}
Thanks, if there are missing data let me know.
Please or to participate in this conversation.