Laravel中如何按日期排序展示Partner关联的四类模型数据制作SOA报表

你需要将四类关联数据转换成统一结构后合并，再按日期排序即可，以下给出两种适用不同场景的实现方式：

方案一：集合合并（适合小数据量场景） #

先取出目标合作伙伴的所有关联数据，分别将每类关联映射为统一字段结构的集合，再合并排序：

// 取出目标合作伙伴
$partner = Partner::findOrFail(1);

// 处理发票数据，映射为统一结构
$invoices = $partner->invoices->map(function ($invoice) {
    return [
        'date' => $invoice->created_at, // 可替换为业务字段，如invoice_date
        'type' => 'partner invoiced us',
        'item' => $invoice->invoice_number,
        // 可自行补充金额、状态等其他需要展示的字段
    ];
});

// 处理账单数据
$bills = $partner->bills->map(function ($bill) {
    return [
        'date' => $bill->created_at,
        'type' => 'us invoiced partner',
        'item' => $bill->bill_number,
    ];
});

// 处理交易数据
$transactions = $partner->transactions->map(function ($transaction) {
    return [
        'date' => $transaction->created_at,
        'type' => $transaction->type === 'debit' ? 'partner paid us' : 'us paid partner',
        'item' => $transaction->type === 'debit' ? 'transaction_debit' : 'transaction_credit',
    ];
});

// 处理贷项通知单数据
$creditNotes = $partner->credit_notes->map(function ($note) {
    return [
        'date' => $note->created_at,
        'type' => 'partner paid us',
        'item' => 'credit_note',
    ];
});

// 合并所有集合后按日期排序，重置集合键
$soaItems = collect()
    ->merge($invoices)
    ->merge($bills)
    ->merge($transactions)
    ->merge($creditNotes)
    ->sortBy('date') // 按日期升序，如需降序换成sortByDesc
    ->values();

方案二：SQL Union查询（适合大数据量/分页场景） #

如果数据量较大，直接在SQL层面用union联合查询后排序，性能更高，也支持原生分页：

use Illuminate\Support\Facades\DB;

$invoicesQuery = DB::table('invoices')
    ->select(
        DB::raw('created_at as date'),
        DB::raw("'partner invoiced us' as type"),
        DB::raw('invoice_number as item')
    )
    ->where('partner_id', 1);

$billsQuery = DB::table('bills')
    ->select(
        DB::raw('created_at as date'),
        DB::raw("'us invoiced partner' as type"),
        DB::raw('bill_number as item')
    )
    ->where('partner_id', 1);

$transactionsQuery = DB::table('transactions')
    ->select(
        DB::raw('created_at as date'),
        DB::raw("IF(type = 'debit', 'partner paid us', 'us paid partner') as type"),
        DB::raw("IF(type = 'debit', 'transaction_debit', 'transaction_credit') as item")
    )
    ->where('partner_id', 1);

$creditNotesQuery = DB::table('credit_notes')
    ->select(
        DB::raw('created_at as date'),
        DB::raw("'partner paid us' as type"),
        DB::raw("'credit_note' as item")
    )
    ->where('partner_id', 1);

// 联合所有查询后按日期排序
$soaItems = $invoicesQuery
    ->union($billsQuery)
    ->union($transactionsQuery)
    ->union($creditNotesQuery)
    ->orderBy('date')
    ->get();

对接Rappasoft Livewire DataTable #

将上述两种方案得到的$soaItems作为数据源传入表格组件，表格的三个列分别绑定date、type、item三个字段即可正常渲染。

注意事项 #

• 确保四类关联数据的日期字段格式统一，避免排序异常
• 如需分页功能，优先使用SQL Union方案，集合合并方案会查询全量数据，性能较差
• 可根据业务需求，在映射/查询阶段补充金额、单据状态等其他需要展示的字段

内容的提问来源于stack exchange，提问作者Mahdi Jafari