1

It's a project using laravel with php as programming language(for website also) and mysql as database. on database, it's 3 tables I used for users, roles, and role_user(this is the relation table). I can manage users as Admin on webpage of my project. However what I want to ask is, how to filter search with relation they had? it's working if it only search the relation(gets everything that had relation I wanted) but failed when added search the word alike for table(gets everything that has the word I search but failed to filter it using the relation)

Using example:

Result I wanted: users with the roles of 'Teacher' and name 'X'

Result I got: users with the name 'X'(somehow the relation code filter gets ignored)

Table that get the result of searching the relation/Table gets every data that had the relation

Supposed Result/User Student Table

Supposed Result/User Teacher Table

Search Table but failed to filter it using relation

Failed Teacher search using relation

Failed Student search using relation

Because the page show up, I guess it's the controllers code that needed to be edited for making my supposed search result.

AdminControllers.php

Models used:

use App\User;
use App\Role;

Codes for tables and search:

/**
     * FOR TEACHER
     *
     */
    // List(this part is working as inteded)
    public function showTeacherList()
    {
        $user = Auth::user(); // Untuk Photo Profile

        $teachers = User::whereHas('roles', function($q){
            $q->where('name', 'Teacher');
        })->get();
        return view('pages.admin.user.teacher.showTeacherList', compact('user', 'teachers') );
    }
    /*
     * This is For Show Search Teacher(Well, the page is working but the filter for relation isn't)
     *
     */
    public function searchTeacher(Request $request)
    {
        $user = Auth::user(); // Untuk Photo Profile
        // menangkap data pencarian
        $search = $request->table_search;
            // This part is the code that supposed to filter the search that has relation to data named Teacher in name column
        $search = User::whereHas('roles', function($q){
            $q->where('name', 'Teacher');
        })->where('name','like',"%".$search."%")
        ->orWhere('nip','like',"%".$search."%")
        ->orWhere('username','like',"%".$search."%")
        ->get();
            // what I got is actually the normal search, so all other relation than Teacher also show up
        return view('pages.admin.user.teacher.showTeacherFiltered', compact('search', 'user') );
    }


/**
     * FOR Student
     *
     */
//Working as intended
    public function showStudentList()
    {
        $user = Auth::user(); // Untuk Photo Profile

        $students = User::whereHas('roles', function($q){
            $q->where('name', 'Student');
        })->get();

        return view('pages.admin.user.student.showStudentList', compact('user', 'students'));
    }
    /*
     * This is For Show Search Student
     *
     */

//Page is working but not the filter using relation
        public function searchStudent(Request $request)
        {
            $user = Auth::user(); // Untuk Photo Profile
            // menangkap data pencarian
            $search = $request->table_search;
                // This part is the code that supposed to filter the search that has relation to data named Student in name column
            $search = User::whereHas('roles', function($q){
                $q->where('name', 'Student');
            })->where('name','like',"%".$search."%")
            ->orWhere('nisn','like',"%".$search."%")
            ->orWhere('username','like',"%".$search."%")
            ->get();
                // // what I got is actually the normal search, so all other relation than Student also show up
            return view('pages.admin.user.student.showStudentFiltered', compact('search', 'user') );
        }

Did anyone know the code to filter search using the relation? it's relation Many-To-Many.

TheBetweener
  • 33
  • 10
  • thins plugin has answers for all of your question https://spatie.be/index.php/docs/laravel-query-builder/v3/introduction – Nipun Tharuksha Aug 27 '21 at 05:25
  • is this already integrated to Laravel or need some step of installation? – TheBetweener Aug 27 '21 at 07:08
  • if not, how to install it? I'm pretty new to laravel – TheBetweener Aug 27 '21 at 07:18
  • Of course you do not need to install a package to do queries in Laravel. Please, make it easy for us to help - which of the methods above do you want help with? Delete the others from the question and focus on that one. "*failed when added search the word alike for table*" - does this mean, for eg a query like "*user with role 'Teacher' and name 'X'*"? What roles do you have? Edit your question and show the basic (relevant) schema of the tables - screenshots on external sites are not useful. See how to create a [minimal, complete, and verifiable example](https://stackoverflow.com/help/mcve). – Don't Panic Aug 27 '21 at 07:43
  • I think maybe this is what you are after? https://stackoverflow.com/questions/20801859/laravel-eloquent-filter-by-column-of-relationship – Don't Panic Aug 27 '21 at 07:44
  • It works fine when I retrieve all data that had relation of that data, but as you said. what I wanted is when I go to specific name, it also retrieve data of that relation. The Roles had 3 data: Admin, Teacher, and Student. But for table it's retrieving either Teacher or Student. but fail to do specific search like your example: user with 'Teacher' roles and name of 'X' – TheBetweener Aug 27 '21 at 08:46
  • if you go through the doc it clearly mention all the steps. Also this is not a package to query in laravel. This is a package to use for filter quering. It almost got 2.9K stars in github.So just have a look. Try to minimize your code all the time instead coding from basic to advance – Nipun Tharuksha Aug 27 '21 at 09:15

0 Answers0