时间戳

首页 > 解决方案 > Laravel 使用嵌套关系计算资源中的数量

sql - Laravel 使用嵌套关系计算资源中的数量

问题描述

我有这个结构：

invoices, invoices_lines, invoices_lines_taxes,taxes

关系：

invoices与 是一对多的invoices_lines。

invoices_linestaxes与中间表是多对多的invoices_lines_taxes

楷模：

Invoice

public function lines()
{
    return $this->hasMany(InvoiceLine::class, 'invoice', 'id');
}

InvoiceLine

public function taxes()
{
    return $this->belongsToMany(Tax::class, 'invoices_lines_taxes', 'line', 'tax');
}

资源：

InvoiceResource

public function toArray($request)
{
    return [
        'id' => $this->id,
        'invoice_date' => $this->invoice_date,
        'reference' => $this->reference,
        'number' => $this->number,
        'description' => $this->description,
        'state' => $this->state,
        'type' => $this->type,
        'lines' => InvoiceLineResource::collection($this->whenLoaded('lines')),
        'amount' => $this->whenLoaded('lines', function () {
            return $this->lines()->sum(DB::raw('unit_price * quantity'));
        }),
    ];
}

我设法计算了 InvoiceResource 上的金额。但我需要用税来计算这些金额。

我该怎么做？

public function toArray($request)
{
    return [
        'id' => $this->id,
        'invoice_date' => $this->invoice_date,
        'reference' => $this->reference,
        'number' => $this->number,
        'description' => $this->description,
        'state' => $this->state,
        'type' => $this->type,
        'lines' => InvoiceLineResource::collection($this->whenLoaded('lines')),
        'amount' => $this->whenLoaded('lines', function () {
            return $this->lines()->sum(DB::raw('unit_price * quantity'));
        }),
        'amount_taxes' => ?????
    ];
}

我可以通过 SQL 进行这些查询来获得税收......但我怎样才能将它翻译成 Laravel 雄辩的方式？

select id from invoices -- get invoice_id.
select id from invoices_lines where invoice = $invoice_id -- get id of invoice_lines
select tax from invoices_lines_taxes where line = $invoice_line_id -- get the id of tax.
select rate from taxes where id = $tax_id

rate 是我必须乘以金额的浮点数（百分比）。所以表达式应该是：invoices_lines.unit_price* invoices_lines.quantity*taxes.rate

标签： sqllaraveleloquentnestedrelationship

解决方案

推荐阅读