Laravel order with GREATEST with MAX by two columns

Question

I looking-for solution to order/sort by relation (hasMany) fields, with max value of two columns.

I prepare starter kit: https://www.db-fiddle.com/f/m371fzUJuQp9dX1yKm4yEm/3

SELECT 
    GREATEST(
        MAX(COALESCE(`to`, 0)),
        MAX(COALESCE(`from`, 0))
    ),
    `parent_id`
FROM salaries
GROUP BY `parent_id`;

The question is, how to display results from parents table, with GREATEST(MAX, MAX) value from two fields, order of course from high to low in Laravel Eloquent ORM?

Expected results:

11 / jedenastka        / 56
13 / trzynastka        / 44
 7 / siodemka          / 23
12 / dwunastka         / 14
10 / dyszka            / 11
 9 / dziewiatka.       /  9
33 / trzydziestktrojka /  1

Answer 1

Maybe it will help someone, it will be useful:

$parents = ParentModel::leftJoin('salaries', 'parents.id', '=', 'salaries.parent_id')
    ->selectRaw('parents.id, parents.name, GREATEST(COALESCE(MAX(salaries.`to`), 0), COALESCE(MAX(salaries.`from`), 0)) as max_value')
    ->groupBy('parents.id', 'parents.name')
    ->orderByDesc('max_value')
    ->get();