如何基于关联模型字段对Eloquent分页查询结果排序？

我编写了一段Eloquent查询，用于分页获取包含关联transaccions、status_report、contracts的Report数据，查询代码如下：

$reports = Report::with([
    "transaccions",
    "status_report",
    "contracts",
])
->where(function ($query) use ($filter, $dates, $type_report, $dataservice) {
    $query
        ->where("type_report", ($dataservice == 'true' ? '<>' : '='), $type_report)
        ->whereBetween("created_at", [$dates[0], $fechas[count($dates) - 1]])
        ->whereHas('status_report', function ($q) {
            $q->whereIn("status", ["VERIFYED"]);
        })
        ->where(function ($query) use ($filter) {
            $query->where("id", "like", '%' . $filter . '%')
                ->orWhereHas('contracts', function ($query) use ($filter) {
                    $query->where('contract', 'like', '%' . $filter . '%')
                        ->orWhereHas("rif", function ($query) use ($filter) {
                            $query->where("rif", 'like', '%' . $filter . '%');
                        })
                        ->orWhere("name", 'like', '%' . $filter . '%');
                })->orWhereHas('transaccions', function ($query) use ($filter) {
                    $query->where('ref', 'like', '%' . $filter . '%')
                        ->orWhere('amount', 'like', '%' . $filter . '%');
                });
        });
})
->paginate($length);

查询返回的结果结构示例：

{
    "id": 45751,
    "status": "VERIFYED",
    "type_report": "MENSUALITY",
    "nota": "",
    "transaccions": [
        {
            "id": 46358,
            "ref": "22380011359",
            "voucher": false
        }
    ],
    "total": "1,28",
    "contracts": [
        {
            "name": "PERSON",
            "contract": "123456",
            "rif": "1122334455",
            "status": "ACTIVE"
        }
    ],
    "created_at": "2022-08-26 14:18:00"
}

需要基于transaccions.amount、contracts.contract等关联模型字段对整个Report结果集排序，但目前只能通过闭包对关联对象内部排序，无法实现整个结果集的排序，请求可行方案。

方法一：使用JOIN关联表直接排序 #

通过JOIN关联对应的表，将关联字段引入主查询，即可直接基于这些字段排序。需注意关联关系类型（如一对一、一对多），如果是一对多，要指定取特定关联记录（比如第一条、最新条）。

以contracts.contract和transaccions.amount为例，假设Report与两个关联表均为一对多关系，取每个Report对应的第一条关联记录字段排序：

$order_by = 'contract'; // 可替换为 'amount'
$order = 'asc'; // 可替换为 'desc'

$reports = Report::with([
    "transaccions",
    "status_report",
    "contracts",
])
->select('reports.*')
// 关联contracts表，取每个report对应的第一条contract的contract字段
->leftJoinSub(
    Contract::select('report_id', 'contract')
        ->orderBy('id')
        ->groupBy('report_id'),
    'latest_contract',
    'reports.id',
    '=',
    'latest_contract.report_id'
)
// 关联transaccions表，取每个report对应的第一条transaccion的amount字段
->leftJoinSub(
    Transaccion::select('report_id', 'amount')
        ->orderBy('id')
        ->groupBy('report_id'),
    'latest_transaccion',
    'reports.id',
    '=',
    'latest_transaccion.report_id'
)
->where(function ($query) use ($filter, $dates, $type_report, $dataservice) {
    // 原where条件保持不变
    $query
        ->where("type_report", ($dataservice == 'true' ? '<>' : '='), $type_report)
        ->whereBetween("created_at", [$dates[0], $fechas[count($dates) - 1]])
        ->whereHas('status_report', function ($q) {
            $q->whereIn("status", ["VERIFYED"]);
        })
        ->where(function ($query) use ($filter) {
            $query->where("id", "like", '%' . $filter . '%')
                ->orWhereHas('contracts', function ($query) use ($filter) {
                    $query->where('contract', 'like', '%' . $filter . '%')
                        ->orWhereHas("rif", function ($query) use ($filter) {
                            $query->where("rif", 'like', '%' . $filter . '%');
                        })
                        ->orWhere("name", 'like', '%' . $filter . '%');
                })->orWhereHas('transaccions', function ($query) use ($filter) {
                    $query->where('ref', 'like', '%' . $filter . '%')
                        ->orWhere('amount', 'like', '%' . $filter . '%');
                });
        });
})
// 根据传入的排序字段选择对应关联子查询字段排序
->when($order_by === 'contract', function ($query) use ($order) {
    $query->orderBy('latest_contract.contract', $order);
})
->when($order_by === 'amount', function ($query) use ($order) {
    // 转换带逗号的字符串数值为decimal类型，避免字符串排序误差
    $query->orderByRaw("CAST(REPLACE(latest_transaccion.amount, ',', '.') AS DECIMAL(10,2)) $order");
})
->paginate($length);

方法二：使用子查询添加排序字段到主查询 #

通过子查询将关联字段的值作为主查询的临时字段，再基于该字段排序，无需修改关联结构，适合轻量化场景：

$order_by = 'amount';
$order = 'desc';

$reports = Report::with([
    "transaccions",
    "status_report",
    "contracts",
])
// 添加关联字段的子查询作为临时字段
->addSelect([
    'contract' => Contract::select('contract')
        ->whereColumn('report_id', 'reports.id')
        ->orderBy('id')
        ->limit(1),
    'amount' => Transaccion::select('amount')
        ->whereColumn('report_id', 'reports.id')
        ->orderBy('id')
        ->limit(1)
])
->where(function ($query) use ($filter, $dates, $type_report, $dataservice) {
    // 原where条件保持不变
    $query
        ->where("type_report", ($dataservice == 'true' ? '<>' : '='), $type_report)
        ->whereBetween("created_at", [$dates[0], $fechas[count($dates) - 1]])
        ->whereHas('status_report', function ($q) {
            $q->whereIn("status", ["VERIFYED"]);
        })
        ->where(function ($query) use ($filter) {
            $query->where("id", "like", '%' . $filter . '%')
                ->orWhereHas('contracts', function ($query) use ($filter) {
                    $query->where('contract', 'like', '%' . $filter . '%')
                        ->orWhereHas("rif", function ($query) use ($filter) {
                            $query->where("rif", 'like', '%' . $filter . '%');
                        })
                        ->orWhere("name", 'like', '%' . $filter . '%');
                })->orWhereHas('transaccions', function ($query) use ($filter) {
                    $query->where('ref', 'like', '%' . $filter . '%')
                        ->orWhere('amount', 'like', '%' . $filter . '%');
                });
        });
})
// 根据临时字段排序
->when($order_by === 'contract', function ($query) use ($order) {
    $query->orderBy('contract', $order);
})
->when($order_by === 'amount', function ($query) use ($order) {
    $query->orderByRaw("CAST(REPLACE(amount, ',', '.') AS DECIMAL(10,2)) $order");
})
->paginate($length);

注意事项 #

• 如果是一对一关联，无需limit(1)或groupBy，直接关联即可。
• 对amount这类带逗号的字符串数值，必须转换为数值类型后再排序，否则会出现字符串排序的逻辑误差（比如"10,00"会排在"2,00"前面）。
• 确保关联表的外键已建立索引，避免查询性能下降。

内容的提问来源于stack exchange，提问作者Marcel Salazar