php - 如何将此 SQL 查询转换为 Eloquent？

Question

我有这个 SQL，它将返回所有转换后的货币汇率的作业，它工作得很好，但是当我们使用 API 资源时，我们必须使用 Eloquent 模型才能像我们配置的那样获取响应数据。

  select * from (
        select 
        jr.id,
        title,description,
        FORMAT(jr.amount,2) as base_amount,
        c.symbol as base_currency,
        FORMAT(IFNULL(jr.amount * currency_rate.rate,jr.amount),2) as amount,
        jr.created_at,
        user_id,type,country_id,city_id,
        delivery_hours,deleted_at
        from job_requests as jr
        left join currencies as c on c.id = jr.currency_id
        left join currency_rates as currency_rate on ((currency_rate.from_currency = c.symbol AND currency_rate.to_currency = "QAR"))
    ) as job_requests order by amount desc ;

我能写这个

$jobRequests = JobRequest::from('job_requests')->select("job_requests.id", "title", "description", DB::raw("format(job_requests.amount, 2) as base_amount"), "c.symbol as base_currency", 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", function($join){
        $join->on("c.id", "=", "job_requests.currency_id");
    })
    ->leftJoin("currency_rates as currency_rate", function($join){
        $join->on('currency_rate.from_currency','=','c.symbol');
        $join->where('currency_rate.to_currency', 'TRY');
        #$join->on('currency_rate.to_currency', '=', DB::raw("c.symbol"));
    })

但我不知道如何将所有这些放在另一个 JobRequest 模型中，以便我可以进行各种排序有子句。

标签： phpmysqllaraveleloquent

Answer 1

通过正确实施雄辩的关系，您可以做到$jobRequests[0]->currency->currencyRate：

class JobRequest extends Model
{
    public function currency()
    {
        return $this->belongsTo(Currency::class);
    }
}

class Currency extends Model
{
    public function currency_rate()
    {
        return $this
            ->belongsTo(CurrencyRate::class, 'from_currency', 'symbol')
            ->where('currency_rate.to_currency', 'TRY');
    }
}