如何将带汇率转换的SQL改写为可封装到模型的Eloquent查询
解决方案
你可以通过Eloquent的本地查询作用域把汇率转换逻辑封装到JobRequest模型中,封装后支持所有原生Eloquent链式操作(排序、条件筛选、having、分页等),同时兼容API Resource的正常使用。
步骤1:在JobRequest模型中定义作用域
// app/Models/JobRequest.php namespace App\Models; use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\Builder; use DB; class JobRequest extends Model { // 可选:将扩展字段加入fillable,方便序列化输出 protected $fillable = [ // 原有字段... 'base_amount', 'base_currency', 'raw_amount', 'raw_base_amount' ]; /** * 本地作用域:为查询追加汇率转换逻辑 * @param Builder $query * @param string $toCurrency 目标货币符号,默认QAR * @return Builder */ public function scopeWithCurrencyConversion(Builder $query, string $toCurrency = 'QAR'): Builder { return $query->select( "job_requests.id", "title", "description", "job_requests.amount as raw_base_amount", // 保留原始金额数值,用于计算、条件判断 DB::raw("FORMAT(job_requests.amount, 2) as base_amount"), "c.symbol as base_currency", DB::raw("IFNULL(job_requests.amount * currency_rate.rate, job_requests.amount) as raw_amount"), // 保留转换后数值,避免字符串排序错误 DB::raw("FORMAT(IFNULL(job_requests.amount * currency_rate.rate, job_requests.amount), 2) as amount"), "job_requests.created_at", "user_id", "type", "country_id", "city_id", "delivery_hours", "deleted_at" ) ->leftJoin("currencies as c", "c.id", "=", "job_requests.currency_id") ->leftJoin("currency_rates as currency_rate", function($join) use ($toCurrency) { $join->on('currency_rate.from_currency','=','c.symbol') ->where('currency_rate.to_currency', $toCurrency); }); } // 可选优化:用访问器处理金额格式化,就不用在select中写FORMAT逻辑,更灵活 public function getFormattedBaseAmountAttribute(): string { return number_format($this->raw_base_amount, 2); } public function getFormattedAmountAttribute(): string { return number_format($this->raw_amount, 2); } }
步骤2:调用示例
封装后可以和普通Eloquent查询完全一样链式调用各种操作:
// 示例1:转换为TRY货币,按转换后金额倒序,筛选金额大于100的职位,分页输出 $jobRequests = JobRequest::withCurrencyConversion('TRY') ->whereNull('deleted_at') ->where('type', 1) ->orderBy('raw_amount', 'desc') // 用数值字段排序,避免字符串排序出现"1000 < 999"的异常 ->having('raw_amount', '>', 100) ->paginate(20); // 示例2:直接查询软删除的职位,转换为默认QAR货币 $deletedJobs = JobRequest::withCurrencyConversion() ->onlyTrashed() ->get();
注意事项
- 代码中单独保留了
raw_base_amount和raw_amount两个数值字段,是为了避免FORMAT后的字符串在排序、数值比较时出现异常,你可以根据业务需求调整是否保留格式化字段 - 如果所有查询都需要默认带汇率转换,可以把逻辑改为全局作用域,无需每次手动调用作用域方法
内容的提问来源于stack exchange,提问作者Ask17
相关产品推荐
相关产品推荐

