Laravel order by a nested eager load relationship

Question

Firstly, I tried looking at this, but I couldn't get the solution to work, as it had pivot tables involved, which mine does not. My brain couldn't abstract the solution from that solution.

I have this query:

$orderLines = PurchasingOrderLine::where('season_id', '=', $seasonRecID)
        ->with('factory')
        ->with('purchasingFactoryDates.milestone')
        ->with('divergedColors.purchasingColorDates.milestone')
        ->get();

So it's quite a big relationship.

I want to sort the purchasingFactoryDates by their milestone's "number" attribute.

Here is a sample of the JSON response for this query (a sample)..

  {
    id: 1,
    season_id: 258,
    factory_id: 38,
    archived: 0,
    created_at: "2016-10-03 00:00:00",
    updated_at: "2016-10-04 00:00:00",
    factory: {
       ID: 38,
       name: "testfac",
       address: "testadrr",
       postCode: "",
       city: "Foo",
       countryID: 27,
       podID: 2,
       warehouse_id: 3,
       paymentTermID: 11,
       factoryCode: "Bazz",
       active: 1
    },
    purchasing_factory_dates: [
      {
        id: 1,
        purchasing_order_line_id: 1,
        milestone_id: 1,
        milestone_date: "1993-10-22 19:15:51",
        milestone_status_id: 2,
        created_at: "2016-10-04 07:38:30",
        updated_at: "2016-10-18 19:15:51",
        milestone: {
          id: 1,
          name: "test1",
          description: null,
          number: 1,
          created_at: "-0001-11-30 00:00:00",
          updated_at: "-0001-11-30 00:00:00"
        }
      },
    .........

Any ideas on how to do this? I don't want the entire relation to be sorted by the Milestone.number, only the purchasingFactoryDates.

I think you have to use the join for that: http://stackoverflow.com/questions/18861186/eloquent-eager-load-order-by — Sefran2, Oct 19 '16 at 13:24

score 1 · Answer 1 · answered Oct 19 '16 at 13:23

1

The easiest way is to join the queries like this:

$orderLines = PurchasingOrderLine::where('season_id', '=', $seasonRecID)
    ->select('purchasing_order_lines.*')
    ->join('purchasing_factory_dates', 'purchasing_factory_dates.purchasing_order_line_id', '=', 'purchasing_order_lines.id')
    ->join('milestone', 'purchasing_factory_dates.milestone_id', '=', 'milestone.id')
    ->with('factory')
    ->with('purchasingFactoryDates.milestone')
    ->with('divergedColors.purchasingColorDates.milestone')
    ->groupBy('purchasing_order_lines.id')
    ->orderBy('milestone.number')
    ->get();

answered Oct 19 '16 at 13:23

Filip Koblański

9,718
4
31
36

1

This is my favourite solution I've looked at so far, as it still brings the Milestones along with the FactoryDates. Thanks for the solution. – k4kuz0 Oct 19 '16 at 13:51
Wait. This didn't sort by the Milestones.number? I have 1, 3, 10, 2 for the Milestone number. :( – k4kuz0 Oct 19 '16 at 14:00
It appears to still be sorted after the purchasing_factory_dates.id – k4kuz0 Oct 19 '16 at 14:05

Sanzeeb Aryal · Answer 2 · 2016-10-19T13:23:53.860

0

That is Constraining eager loading

$orderLines = PurchasingOrderLine::with('purchasingFactoryDates'=>function($query)
                            {
                              $query->with(['milestone'=>function($query)
                                   {
                                      $query->orderBy('number');
                                    }]);
                            }]);

edited Oct 19 '16 at 13:23

answered Oct 19 '16 at 13:22

Sanzeeb Aryal

4,358
3
20
43

Can this just be plopped into the middle of all of the other "with" methods that I am calling? – k4kuz0 Oct 19 '16 at 13:23
2

Well but this will only order the millestones inside the relation not every records by mileston's number – Filip Koblański Oct 19 '16 at 13:25

user1669496 · Answer 3 · 2016-10-19T14:26:03.823

0

I think this is likely what you want which matches up close to the other answer you were looking at.

$orderLines = PurchasingOrderLine::where('season_id', '=', $seasonRecID)
    ->with('factory')
    ->with(['purchasingFactoryDates' => function($q) {
        $q->select('purchasingFactoryDates.*')->join('milestone', 'purchasingFactoryDates.milestone_id', '=', 'milestone.id')->orderBy('milestone.number');
    }, 'purchasingFactoryDates.milestone'])
    ->with('divergedColors.purchasingColorDates.milestone')
    ->get();

edited Oct 19 '16 at 14:26

answered Oct 19 '16 at 13:26

user1669496

32,176
9
73
65

This doesn't sort by the milestone number. This still for me sorts by the purchasing_factory_date id. – k4kuz0 Oct 19 '16 at 14:08
This sorts correctly if I remove the `->with('purchasingFactoryDates.milestone')`. Somehow this overrides the sorting? So in that sense, I don't get the milestones in the result, but the purchasing_order_lines are sorted correctly? – k4kuz0 Oct 19 '16 at 14:13

Laravel order by a nested eager load relationship

3 Answers3