如何使用spatie/laravel-query-builder实现带关联关系的数据筛选与搜索？（Laravel框架）

Hey there! Let's start by fixing the core issue in your original code—then we'll refactor it to use spatie/laravel-query-builder for cleaner, more maintainable code.

Why Your Original Query Isn't Working #

The problem is with how your orWhere clauses are structured. Right now, your query logic looks like this:

(User has a Student role AND name matches search) OR nisn matches search OR username matches search

That's why non-Student users are showing up—if their nisn or username matches the keyword, they bypass the role check. To fix this, you need to group the three "match keyword" conditions into a single logical block, so the final logic becomes:

User has a Student role AND (name matches OR nisn matches OR username matches)

Fixed Native Laravel Query #

Here's how to adjust your original code:

public function searchStudent(Request $request) {
    $user = Auth::user(); // For avatar
    $searchTerm = $request->table_search;

    $filteredStudents = User::whereHas('roles', function($q){
        $q->where('name', 'Student');
    })->where(function($query) use ($searchTerm) {
        // Group these conditions so they're all checked together
        $query->where('name','like',"%{$searchTerm}%")
              ->orWhere('nisn','like',"%{$searchTerm}%")
              ->orWhere('username','like',"%{$searchTerm}%");
    })->get();

    return view('pages.admin.user.student.showStudentFiltered', compact('filteredStudents', 'user') );
}

Implementing with spatie/laravel-query-builder #

Now, let's refactor this to use the package for a more scalable solution (great if you ever need to add more filters later).

Step 1: Make Sure the Package is Installed #

First, if you haven't already, install the package via Composer:

composer require spatie/laravel-query-builder

Step 2: Refactor the Controller Method #

We'll use QueryBuilder to encapsulate our filters, making the code cleaner and adding built-in protection against invalid filter parameters.

use Spatie\QueryBuilder\QueryBuilder;
use Spatie\QueryBuilder\AllowedFilter;

public function searchStudent(Request $request) {
    $user = Auth::user();
    $searchTerm = $request->table_search;

    $filteredStudents = QueryBuilder::for(User::class)
        // Define allowed filters to prevent arbitrary database queries
        ->allowedFilters([
            // Filter to only include users with the Student role
            AllowedFilter::callback('student_role', function ($query) {
                $query->whereHas('roles', fn($q) => $q->where('name', 'Student'));
            }),
            // Multi-field search for name, nisn, username
            AllowedFilter::callback('search', function ($query, $value) {
                $query->where(function($q) use ($value) {
                    $q->where('name', 'like', "%{$value}%")
                      ->orWhere('nisn', 'like', "%{$value}%")
                      ->orWhere('username', 'like', "%{$value}%");
                });
            }),
        ])
        // Apply our fixed filters: enforce Student role + apply search term if present
        ->filter([
            'student_role' => true,
            ...($searchTerm ? ['search' => $searchTerm] : [])
        ])
        ->get();

    return view('pages.admin.user.student.showStudentFiltered', compact('filteredStudents', 'user') );
}

Alternative: Simplify if Student Role is Always Required #

If this endpoint will always filter for Student users (no need to toggle it), you can simplify the query by starting with the role filter directly:

$filteredStudents = QueryBuilder::for(User::whereHas('roles', fn($q) => $q->where('name', 'Student')))
    ->allowedFilters([
        AllowedFilter::callback('search', function ($query, $value) {
            $query->where(function($q) use ($value) {
                $q->where('name', 'like', "%{$value}%")
                  ->orWhere('nisn', 'like', "%{$value}%")
                  ->orWhere('username', 'like', "%{$value}%");
            });
        }),
    ])
    ->when($searchTerm, fn($query) => $query->filter(['search' => $searchTerm]))
    ->get();

Key Benefits of Using spatie/laravel-query-builder #

• Maintainability: Add new filters later by just adding to allowedFilters
• Security: Automatically blocks invalid filter parameters from being applied
• Readability: Makes complex filtering logic more explicit and easy to follow

内容的提问来源于stack exchange，提问作者TheBetweener