Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

The orderBy method can be applied to a query multiple times:

Employee::orderBy('type_id', 'asc')->orderBy('lastname', 'asc')->get();

(Or with whatever other query components you need)

Last updated 8 years ago.
0

@tdhsmith thanks for your answer. Now what about a relationship?

The employee has a contract which contains the employee_id and the type_id. How would it be possible to group the Employee result by contract.type_id and ordered by lastname ?

0

Max13 said: How would it be possible to group the Employee result by contract.type_id and ordered by lastname ?

I don't believe orderBy and groupBy are capable of referring to relations like that unfortunately.

You could do a classic join, but you lose some of Eloquent's DB abstraction:

Employee::join('contracts as contract', 'contract.employee_id', '=', 'employees.id')
    ->groupBy('contract.type_id')
    ->select('employees.*') // stop the joined table from overwriting columns with the same name
    ->with('contracts') 
    ->orderBy('lastname', 'asc')
    ->get();

Alternatively if you aren't paginating the results (or using other features that require the sorting to be done in the query), you could let Laravel's Collection methods do the work instead of the database:

Employee::with('contracts')
    ->get()  // from here on we are dealing with a Collection, not the query builder!
    ->groupBy('employee.type_id')  // most Collection functions can use dot notation
    ->sortBy('lastname');
Last updated 8 years ago.
0
Solution

@tdhsmith: Thank you a lot for your answer, it's closer to my needs. Your second option will be my last chance, so I prefer to keep is aside for now.

Strangely, I only get 3 results, 2 duplicates Employees (certainly because this employee has more than 1 contracts). I modified your query and I have something better

Employee::leftJoin('contracts as contract', 'contract.employee_id', '=', 'employees.id')
        ->select('employees.*')
        ->distinct()
        ->with(['contracts' => function ($c) {
            $c->withTrashed();
        }])
        ->orderBy('contract.type_id')
        ->orderBy('lastname')
        ->get(),

leftJoin allows me to get results with no Contracts, removing groupBy shows all the Employees, distinct() because I got multiple results if Employee has more that 1 Contracts and finally orderBy successively returns the order expected.

I think I have the good query, but I know that leftJoin shows the results from left table even if no results on right table. How can I retrieve results from right table (employees) with no results on the left table (contracts)?

0

Sign in to participate in this thread!

Eventy

Your banner here too?

Max13 max13 Joined 20 Mar 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.