英文:
MySQL & Laravel: How to include my custom derived column in where clauses?
问题
我正在处理一个根据不同标准筛选用户的函数。其中一个筛选条件是基于用户净资产。在数据库中，我们目前只存储净资产和净债务，计算净资产只是从资产中减去债务。
我的筛选函数需要能够找到低于目标数字的用户、高于目标数字的用户或在两个目标数字之间的用户。
在 Eloquent 代码中，这是我所拥有的：
//更多代码...
// 筛选净资产
->when(isset($request_filter_params['minNetWorth'])
|| isset($request_filter_params['maxNetWorth']), fn ($query) =>
$query->whereHas('net_worth_estimate_calc_response', fn ($q) =>
$q->select(DB::raw('(net_worth_estimate_asset - net_worth_estimate_debt) as net_worth'))
->when(isset($request_filter_params['minNetWorth']), fn ($qq) =>
$qq->where('net_worth', '>=', $request_filter_params['minNetWorth'])
)
->when(isset($request_filter_params['maxNetWorth']), fn ($qqq) =>
$qqq->where('net_worth', '<=', $request_filter_params['maxNetWorth'])
)
)
)
//更多代码...
它生成的 SQL 如下：
select
*
from
`users`
where
and `is_active` = 1
and `is_groupadmin` = 0
and exists (
select
(net_worth_estimate_asset - net_worth_estimate_debt) as net_worth
from
`user_net_worth_estimate_calculator_responses`
where
`users`.`id` = `user_net_worth_estimate_calculator_responses`.`user_id`
and `net_worth` >= 2500
and `net_worth` <= 10000)
order by
`updated_at` desc
这个 SQL 产生错误 Unknown column 'net_worth' in 'where clause'。
显然是由于我自定义的 DB::raw('(net_worth_estimate_asset - net_worth_estimate_debt) as net_worth') 调用引起的。有没有办法允许基于这个派生值在 where 子句中进行筛选？还是 MySQL 无法做到这一点，我必须将其移到 PHP 层（我很想避免这样做）？
编辑：'user_net_worth_estimate_calculator_responses' 只包含 user_id (bigint)、net_worth_estimate_asset (double)、net_worth_estimate_debt (double)、一个主键 id 和一些时间戳。模型文件是一个完全标准的 Eloquent 模型。
英文:
I am working on a function that filters users based on varied criteria. One of the filters is based on user net worth. In the database, we currently only store net assets, and net debt, and calculating net worth is simply subtracting debt from assets.
My filter function needs to be able to find users under a target number, over a target number, or between two target numbers.
In Eloquent code, here is what I have.
//more code before...
// filter net worth
->when(isset($request_filter_params['minNetWorth'])
|| isset($request_filter_params['maxNetWorth']), fn ($query) =>
$query->whereHas('net_worth_estimate_calc_response', fn ($q) =>
$q->select(DB::raw('(net_worth_estimate_asset - net_worth_estimate_debt) as net_worth'))
->when(isset($request_filter_params['minNetWorth']), fn ($qq) =>
$qq->where('net_worth', '>=', $request_filter_params['minNetWorth'])
)
->when(isset($request_filter_params['maxNetWorth']), fn ($qqq) =>
$qqq->where('net_worth', '<=', $request_filter_params['maxNetWorth'])
)
)
)
//more code after...
The SQL it produces:
select
*
from
`users`
where
and `is_active` = 1
and `is_groupadmin` = 0
and exists (
select
(net_worth_estimate_asset - net_worth_estimate_debt) as net_worth
from
`user_net_worth_estimate_calculator_responses`
where
`users`.`id` = `user_net_worth_estimate_calculator_responses`.`user_id`
and `net_worth` >= 2500
and `net_worth` <= 10000)
order by
`updated_at` desc
That sql produces the error Unknown column 'net_worth' in 'where clause'.
Clearly caused by my custom DB::raw('(net_worth_estimate_asset - net_worth_estimate_debt) as net_worth') call, is there any way to allow filtering in the where clauses based on this derived value? Or is this something MySQL cant do and I have to move this into the PHP layer (which I would love to avoid)?
Edit: 'user_net_worth_estimate_calculator_responses' only has user_id (bigint), net_worth_estimate_asset (double), net_worth_estimate_debt (double, a PK id and a couple of timestamps. The Model file is a completely standard eloquent model.
答案1
得分: 0
你可以尝试使用 having：
$qq->having('net_worth', '>=', $request_filter_params['minNetWorth'])
或者使用 whereRaw（这 应该 会有更好的性能）：
$qq->whereRaw('net_worth_estimate_asset - net_worth_estimate_debt >= ?', $request_filter_params['minNetWorth'])
英文:
You can try with having:
$qq->having('net_worth', '>=', $request_filter_params['minNetWorth'])
or alternatively with whereRaw (this should have better performance)
$qq->whereRaw('net_worth_estimate_asset - net_worth_estimate_debt >= ?', $request_filter_params['minNetWorth'])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论