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: stackoverflow.com/questions/18861186/… — Sefran2
– Sefran2, Commented Oct 19, 2016 at 13:24

Filip Koblański · Accepted Answer · 2016-10-19 13:23:58Z

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, 2016 at 13:23

Filip Koblański

10k4 gold badges33 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

k4kuz0 Over a year ago

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 Over a year ago

Wait. This didn't sort by the Milestones.number? I have 1, 3, 10, 2 for the Milestone number. :(

k4kuz0 Over a year ago

It appears to still be sorted after the purchasing_factory_dates.id

Sanzeeb Aryal · Accepted Answer · 2016-10-19 13:23:53Z

0

That is Constraining eager loading

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

edited Oct 19, 2016 at 13:23

answered Oct 19, 2016 at 13:22

Sanzeeb Aryal

3,2663 gold badges23 silver badges44 bronze badges

2 Comments

k4kuz0 Over a year ago

Can this just be plopped into the middle of all of the other "with" methods that I am calling?

Filip Koblański Over a year ago

Well but this will only order the millestones inside the relation not every records by mileston's number

user1669496 · Accepted Answer · 2016-10-19 14:26:03Z

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, 2016 at 14:26

answered Oct 19, 2016 at 13:26

user1669496

33.3k9 gold badges76 silver badges65 bronze badges

2 Comments

k4kuz0 Over a year ago

This doesn't sort by the milestone number. This still for me sorts by the purchasing_factory_date id.

k4kuz0 Over a year ago

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?

Collectives™ on Stack Overflow

Laravel order by a nested eager load relationship

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related