深入理解Laravel Eloquent复杂查询：关联模型与多条件搜索优化

// app/Models/User.php
class User extends Authenticatable
{
    /**
     * 定义用户搜索的局部作用域。
     * @param \Illuminate\Database\Eloquent\Builder $query
     * @param string $searchValues
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeSearch($query, $searchValues)
    {
        return $query->where('first_name', 'like', '%' . $searchValues . '%')
                     ->orWhere('last_name', 'like', '%' . $searchValues . '%')
                     ->orWhere('email', 'like', '%' . $searchValues . '%');
    }

    /**
     * 用户与部门的多对多关系。
     */
    public function departments()
    {
        return $this->belongsToMany(Department::class, 'users_departments', 'user_id', 'department_id');
    }

    /**
     * 用户与角色的多对多关系。
     */
    public function roles()
    {
        return $this->belongsToMany(Role::class, 'users_roles', 'user_id', 'role_id');
    }
}

// app/Models/Department.php
class Department extends Model
{
    // ...
}

// app/Models/Role.php
class Role extends Model
{
    // ...
}

// 错误的查询尝试
$searchValues = 'some_search_term';
$orderColumnName = 'id';
$order = 'asc';
$requestLength = 10;

$query = User::with([
    'roles' => function($query) use($searchValues) {
        return $query->where('title', 'LIKE','%'.$searchValues.'%');
    },
    'departments' => function($query) use($searchValues) {
        return $query->where('title', 'LIKE','%'.$searchValues.'%');
    }
])
->search($searchValues)
->orderBy($orderColumnName, $order)
->limit($requestLength)
->get();

// app/Http/Controllers/UserController.php (示例)
use App\Models\User;
use Illuminate\Http\Request;

class UserController extends Controller
{
    public function index(Request $request)
    {
        $searchValues = $request->input('search', ''); // 获取搜索值
        $orderColumnName = $request->input('order_by', 'id'); // 排序字段
        $order = $request->input('order', 'asc'); // 排序方式
        $length = $request->input('length', 10); // 每页记录数

        // 定义一个闭包，用于搜索关联模型的标题字段，避免重复代码
        $titleSearchClosure = function ($query) use ($searchValues) {
            return $query->where('title', 'LIKE', "%{$searchValues}%");
        };

        $users = User::with(['roles' => $titleSearchClosure, 'departments' => $titleSearchClosure]) // 预加载关联数据，并限制预加载结果
            ->search($searchValues) // 搜索用户表直接字段 (first_name, last_name, email)
            ->orWhereHas('roles', $titleSearchClosure) // 或搜索关联的 roles 表 title 字段
            ->orWhereHas('departments', $titleSearchClosure) // 或搜索关联的 departments 表 title 字段
            ->orderBy($orderColumnName, $order) // 排序
            ->limit($length) // 限制返回数量
            ->get(); // 执行查询并获取结果

        return response()->json($users);
    }
}

SELECT users.id, users.first_name, users.last_name, users.email, roles.title AS role_title, departments.title AS department_title
FROM users
LEFT JOIN users_departments ON users_departments.user_id = users.id
LEFT JOIN departments ON departments.id = users_departments.department_id
LEFT JOIN users_roles ON users_roles.user_id = users.id
LEFT JOIN roles ON roles.id = users_roles.role_id
WHERE (
    users.first_name LIKE '%search%' OR
    users.last_name LIKE '%search%' OR
    users.email LIKE '%search%'
)
OR EXISTS (SELECT 1 FROM users_departments ud JOIN departments d ON ud.department_id = d.id WHERE ud.user_id = users.id AND d.title LIKE '%search%')
OR EXISTS (SELECT 1 FROM users_roles ur JOIN roles r ON ur.role_id = r.id WHERE ur.user_id = users.id AND r.title LIKE '%search%')
GROUP BY users.id -- 如果需要去重，否则可能返回多行
ORDER BY ...
LIMIT ...