lundi 24 juillet 2023

Sort an eloquent query based on the field of a nested model

I have this eloquent query that brings me 10 reports in a paginated manner with their respective transactions and contracts

            $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);

This query shows a result that is processed in another function and shows something like this

        {
            "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"
        },

These results are displayed in a table and I would like to be able to sort the results based on transactions.amount, contracts.contract (or any other field from the nested models) but I can't find the way to do it, I know that you can order the nested models in this way:

             "transaccions" => function ($query) use ($order_by, $order) {
                    $query->orderBy($order_by, $order);
                },

but I need is to sort all the results, because that only sorts me the nested objects, thanks.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire