sql - 仅获取 Laravel 中连接表的最后更新行

Question

我有以下代码用于从数据库中获取数据，

        $invoices = SalesInvoices::where('is_invoice', 1)
        ->join('customers', 'customers.customer_id', '=', 'sales_invoices.customer_id')
        ->join('sales_payments', 'sales_payments.invoice_id', '=', 'sales_invoices.invoice_id')
        ->orderBy("sales_payments.created_at", "desc") //Get the latest updated payment for view in table for just 1 row.
        ->get();

Sales_payments表具有不同的付款阶段，其中包含多个条目，例如 Received、Partial、Completed 等，并且每个阶段invoice_id的数据行都有哪个是SalesInvoices.

现在我在 中显示所有发票DataTables，但是使用上面的代码，我得到了 的所有值sales_payments，但我只需要最后更新的条目，它具有最新的created_at.

我怎样才能只获得一个发票行但付款的最新状态Sales_payments，

谢谢，

标签： sqllaraveleloquent

Answer 1

有多项选择：a。这是因为您已经在 desc 订购了 salespayment.created，获得第一个

$invoices = SalesInvoices::where('is_invoice', 1)
        ->join('customers', 'customers.customer_id', '=', 'sales_invoices.customer_id')
        ->join('sales_payments', 'sales_payments.invoice_id', '=', 'sales_invoices.invoice_id')
        ->orderBy("sales_payments.created_at", "desc") //Get the latest updated payment for view in table for just 1 row.
        ->first();

湾。使用最新

$invoices = SalesInvoices::where('is_invoice', 1)
            ->join('customers', 'customers.customer_id', '=', 'sales_invoices.customer_id')
            ->join('sales_payments', 'sales_payments.invoice_id', '=', 'sales_invoices.invoice_id')
            ->latest('sales_payments.created_at')->first();

C。最后的

$invoices = SalesInvoices::where('is_invoice', 1)
            ->join('customers', 'customers.customer_id', '=', 'sales_invoices.customer_id')
            ->join('sales_payments', 'sales_payments.invoice_id', '=', 'sales_invoices.invoice_id')
            ->last();