You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Laravel查询未正确应用过滤参数问题求助

avatar
AI 小北
2026-6-21
问题：费用管理Web应用过滤参数未生效，SQL查询未包含过滤条件

我正在开发一款费用管理Web应用，在分析页面实现过滤功能时遇到问题：前端已将过滤参数正确发送至服务器，但生成的原始SQL查询并未包含这些过滤参数。

相关代码

前端代码（Vue） 

async getCategories() {
    this.loading = true;

    const { response } = await this.request('GET', '/get-other-categories', { params: { userId: this.activeUser.id, ...this.filters } });

    this.categories = response.data;
    this.expenses = this.categories.reduce((sum, item) => sum + item.total_expenses, 0);
    
    this.loading = false;
},
async getIncomeCategory() {
    this.loading = true;

    const { response } = await this.request('GET', '/get-income-category', { params: { userId: this.activeUser.id, ...this.filters } });

    this.incomeCategories = response.data;
    this.income = this.incomeCategories.reduce((sum, item) => sum + item.total_income, 0);

    this.loading = false;
},

filterRecordsByAccounts(accounts) {
    const selectedAccounts = [...new Set(accounts)];

    if (!selectedAccounts.length) {
        this.filters.accounts = [];

        this.getCategories();
        this.getIncomeCategory();

        return;
    }

    this.filters.accounts = selectedAccounts;

    this.getCategories();
    this.getIncomeCategory();
},

后端代码（Laravel） 

public function getIncomeCategory(Request $request): JsonResponse
{
    $userId = $request->query('userId');
    $startDate = $request->query('startDate');
    $endDate = $request->query('endDate');
    $accountIds = $request->query('accounts', []);
    $categoryIds = $request->query('categories', []);
    $labels = $request->query('labels', []);
    $types = $request->query('types', []);
    $paymentTypes = $request->query('paymentTypes', []);
    $paymentStatuses = $request->query('paymentStatuses', []);

    Log::info('received-parameters', [
        'userId' => $userId,
        'startDate' => $startDate,
        'endDate' => $endDate,
        'accountIds' => $accountIds,
        'categoryIds' => $categoryIds,
        'labels' => $labels,
        'types' => $types,
        'paymentTypes' => $paymentTypes,
        'paymentStatuses' => $paymentStatuses,
    ]);

    $query = Category::with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
        $query->where('visible', true)
            ->with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
                $query->where('user_id', $userId)
                    ->where('visible', true)
                    ->with(['records' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
                        $query->where('user_id', $userId)
                            ->whereBetween('date', [$startDate, $endDate])
                            ->when($accountIds, function ($query, $accountIds) {
                                $query->whereIn('account_id', $accountIds);
                            })
                            ->when($categoryIds, function ($query, $categoryIds) {
                                $query->whereIn('category_id', $categoryIds);
                            })
                            ->whereHas('labels', function ($q) use ($labels) {
                                $q->whereIn('label_id', $labels);
                            })
                            ->when($types, function ($query, $types) {
                                $query->whereIn('type', $types);
                            })
                            ->when($paymentTypes, function ($query, $paymentTypes) {
                                $query->whereIn('payment_type', $paymentTypes);
                            })
                            ->when($paymentStatuses, function ($query, $paymentStatuses) {
                                $query->whereIn('payment_status', $paymentStatuses);
                            });
                    }]);
            }]);
    }])
    ->whereNull('parent_id')
    ->where('visible', true)
    ->where(function ($query) {
        $query->whereRaw("json_extract(name, '$.en') = 'Income'")
            ->orWhereRaw("json_extract(name, '$.lv') = 'Ienākumi'");
    });

    Log::info('query', [$query->toSql()]);

    $categories = $query->get();
    
    return response()->json(CategoryResource::collection($categories));
}

public function getOtherCategories(Request $request): JsonResponse
{
    $userId = $request->query('userId');
    $startDate = $request->query('startDate');
    $endDate = $request->query('endDate');
    $accountIds = $request->query('accounts', []);
    $categoryIds = $request->query('categories', []);
    $labels = $request->query('labels', []);
    $types = $request->query('types', []);
    $paymentTypes = $request->query('paymentTypes', []);
    $paymentStatuses = $request->query('paymentStatuses', []);

    Log::info('received-parameters', [
        'userId' => $userId,
        'startDate' => $startDate,
        'endDate' => $endDate,
        'accountIds' => $accountIds,
        'categoryIds' => $categoryIds,
        'labels' => $labels,
        'types' => $types,
        'paymentTypes' => $paymentTypes,
        'paymentStatuses' => $paymentStatuses,
    ]);

    $query = Category::with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
        $query->where('visible', true)
            ->with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
                $query->where('user_id', $userId)
                    ->where('visible', true)
                    ->with(['records' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
                        $query->where('user_id', $userId)
                            ->whereBetween('date', [$startDate, $endDate])
                            ->when($accountIds, function ($query, $accountIds) {
                                $query->whereIn('account_id', $accountIds);
                            })
                            ->when($categoryIds, function ($query, $categoryIds) {
                                $query->whereIn('category_id', $categoryIds);
                            })
                            ->whereHas('labels', function ($q) use ($labels) {
                                $q->whereIn('label_id', $labels);
                            })
                            ->when($types, function ($query, $types) {
                                $query->whereIn('type', $types);
                            })
                            ->when($paymentTypes, function ($query, $paymentTypes) {
                                $query->whereIn('payment_type', $paymentTypes);
                            })
                            ->when($paymentStatuses, function ($query, $paymentStatuses) {
                                $query->whereIn('payment_status', $paymentStatuses);
                            });
                    }]);
            }]);
    }])
    ->whereNull('parent_id')
    ->where('visible', true)
    ->where(function ($query) {
        $query->whereRaw("json_extract(name, '$.en') != 'Income'")
            ->orWhereRaw("json_extract(name, '$.lv') != 'Ienākumi'");
    });
    
    Log::info('query', [$query->toSql()]);

    $categories = $query->get();

    return response()->json(CategoryResource::collection($categories));
}

日志信息

尝试按银行账户过滤时，日志输出如下：

[2024-07-05 02:44:21] local.INFO: received-parameters {"userId":"1","startDate":null,"endDate":null,"accountIds":["1"],"categoryIds":[],"labels":[],"types":[],"paymentTypes":[],"paymentStatuses":[]} 
[2024-07-05 02:44:21] local.INFO: query ["select * from `categories` where `parent_id` is null and `visible` = ? and (json_extract(name, '$.en') != 'Income' or json_extract(name, '$.lv') != 'Ienākumi') and `categories`.`deleted_at` is null"] 
[2024-07-05 02:44:24] local.INFO: received-parameters {"userId":"1","startDate":null,"endDate":null,"accountIds":["1"],"categoryIds":[],"labels":[],"types":[],"paymentTypes":[],"paymentStatuses":[]} 
[2024-07-05 02:44:24] local.INFO: query ["select * from `categories` where `parent_id` is null and `visible` = ? and (json_extract(name, '$.en') = 'Income' or json_extract(name, '$.lv') = 'Ienākumi') and `categories`.`deleted_at` is null"]

可以看到服务器已经收到accountIds参数，但生成的SQL查询里完全没有包含账户过滤的条件，请问该如何解决？

解决方案

问题原因

  1. toSql()的局限性：你打印的$query->toSql()只输出主查询（categories表）的SQL，而过滤条件写在**关联预加载（with）**的闭包里，这部分SQL不会包含在主查询中，而是会在后续加载关联数据时单独执行。
  2. 潜在逻辑漏洞：
    • whereBetween('date', [$startDate, $endDate])在startDate或endDate为null时会直接报错
    • whereHas('labels', ...)当labels为空数组时，会过滤掉所有无标签的记录，不符合预期

修复后的代码 

public function getIncomeCategory(Request $request): JsonResponse
{
    $userId = $request->query('userId');
    $startDate = $request->query('startDate');
    $endDate = $request->query('endDate');
    $accountIds = $request->query('accounts', []);
    $categoryIds = $request->query('categories', []);
    $labels = $request->query('labels', []);
    $types = $request->query('types', []);
    $paymentTypes = $request->query('paymentTypes', []);
    $paymentStatuses = $request->query('paymentStatuses', []);

    Log::info('received-parameters', [
        'userId' => $userId,
        'startDate' => $startDate,
        'endDate' => $endDate,
        'accountIds' => $accountIds,
        'categoryIds' => $categoryIds,
        'labels' => $labels,
        'types' => $types,
        'paymentTypes' => $paymentTypes,
        'paymentStatuses' => $paymentStatuses,
    ]);

    $query = Category::with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
        $query->where('visible', true)
            ->with(['children' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
                $query->where('user_id', $userId)
                    ->where('visible', true)
                    ->with(['records' => function ($query) use ($userId, $startDate, $endDate, $accountIds, $categoryIds, $labels, $types, $paymentTypes, $paymentStatuses) {
                        $query->where('user_id', $userId)
                            // 修复日期判断，避免null报错
                            ->when($startDate && $endDate, function ($query) use ($startDate, $endDate) {
                                $query->whereBetween('date', [$startDate, $endDate]);
                            })
                            ->when($accountIds, function ($query, $accountIds) {
                                $query->whereIn('account_id', $accountIds);
                            })
                            ->when($categoryIds, function ($query, $categoryIds) {
                                $query->whereIn('category_id', $categoryIds);
                            })
                            // 修复标签过滤逻辑，空数组时不应用过滤
                            ->when($labels, function ($query) use ($labels) {
                                $query->whereHas('labels', function ($q) use ($labels) {
                                    $q->whereIn('label_id', $labels);
                                });
                            })
                            ->when($types, function ($query, $types) {
                                $query->whereIn('type', $types);
                            })
                            ->when($paymentTypes, function ($query, $paymentTypes) {
                                $query->whereIn('payment_type', $paymentTypes);
                            })
                            ->when($paymentStatuses, function ($query, $paymentStatuses) {
                                $query->whereIn('payment_status', $paymentStatuses);
                            });
                    }]);
            }]);
    }])
    ->whereNull('parent_id')
    ->where('visible', true)
    ->where(function ($query) {
        $query->whereRaw("json_extract(name, '$.en') = 'Income'")
            ->orWhereRaw("json_extract(name, '$.lv') = 'Ienākumi'");
    });

    // 查看所有执行的SQL，包括关联查询
    DB::listen(function ($query) {
        Log::info('full-query', ['sql' => $query->sql, 'bindings' => $query->bindings]);
    });

    $categories = $query->get();
    
    return response()->json(CategoryResource::collection($categories));
}

// getOtherCategories方法执行相同修复即可

验证方式

替换原有的Log::info('query', [$query->toSql()])为上述DB::listen代码，就能看到所有执行的SQL语句（包括关联预加载的查询），确认过滤条件是否生效。

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.21 08:44:50