Group Eloquent query by column, order by another

replied 8 years ago

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

max13

replied 8 years ago

@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

tdhsmith

replied 8 years ago

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

max13

replied 8 years ago

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

Moderators