Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

manuelbosi's avatar

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.

0 likes
0 replies

Please or to participate in this conversation.