OrderBy with 'relationship' in laravel eloquent

Question

I am having following tables,

User (id, name)
category (id, category_name, order)
user_category (id, user_id, category_id)
specialties (id, spe_name)
user_specialities (id, spe_id, user_id)

I am trying to get all the users with all related specialties order by 'order' in a category where each user has under category in table 'user_category'.

I have tried with relationship 'with' but I am not able to orderby on this query.

Any thoughts where I may wrong?

$query = Coach::with(['specialities','specialities.speciality','coachUnderCategories', 'coachUnderCategories.coachCategory']) ->orderBy('coach_categories.order', 'desc'); — Ghost Rider
Yes, I tried this way as well but data "with"specialties relationship is always null. — Ghost Rider
there is no coach_categories model function which you with, follow the below ans which i give then works. — Jasim Juwel
In your answer, 'supplier.id' what is supplier? is it a table name or relationship property? — Ghost Rider

Jane Jane · Accepted Answer · 2018-12-13T17:06:50

With() makes multiple queries using sql operator whereIn I think, therefore you don't have the field available when you're trying to orderBy on it.

There are two ways you can go about this,

either you join() the tables together and then orderBy on the joined field, or you sort the collection the query results in like this

$sortedResult = SomeModel::with('all your relations')->get()
    ->sortByDesc(function ($anItem) {
        return $anItem->category->order;
    })->all();

If you wanna go with the join, here's another post: Sort collection by relationship value

Edit according to comment

User::join('user_category', 'user_category.user_id', '=', 'user.id')
    ->join('user_specialities', 'user_specialities.user_id', '=', 'user.id')
    ->select('user.*')
    ->selectRaw('(select group_concat(spe_name) from specialities where specialities.id = user_specialiliteies.spe_id) as specialities')
    ->orderBy('user_category.order')
    ->get();

OrderBy with 'relationship' in laravel eloquent

1 Answers