Laravel order collection based on property of a one to many related relationship

Question

I have a model called Business(having columns description, name, view etc), it has a one to many relationship with the model "Rating". I want to query my Business model such that it returns a result that check if a search string is present in name or description and also sorts by the business having highest rating first then views the business has.

I have written a code for the search query. find below:

$business = Business::with('images')
    ->where('isActive', true)
    ->where(function ($q) use ($query) {
        $q->where('name', 'LIKE', '%' . $query . '%')
            ->orWhere('description', 'LIKE', '%' . $query . '%');
    })->get();

I know that I can fetch the average ratings of the business like this:

$averageRating = Rating::where('business_id', $id)->avg('rating');
$business = Business::with(['images'])->get();

foreach ($business as $item) {
    $item["rating"] = floor($averageRating);
}

So, how do I order based on highest ratings first, then highest views

Possible duplicate question - This should help: https://stackoverflow.com/questions/18143061/laravel-orderby-on-a-relationship — Friendly Code, May 28 '20 at 08:25
Does this answer your question? [Laravel orderBy on a relationship](https://stackoverflow.com/questions/18143061/laravel-orderby-on-a-relationship) — Friendly Code, May 28 '20 at 08:27
Why comment 2 times an answer which doesn't fit the question ? — Vincent Decaux, May 28 '20 at 08:27

OMR · Answer 1 · 2020-05-28T10:38:28.747

1

you can use Subquery Selects:

business = Business::with('images')
        ->where('isActive', true)
                    ->where(function ($q) use ($query){
                        $q->where('name', 'LIKE','%'.$query.'%')->orWhere('description', 'LIKE','%'.$query.'%');
                    })
->orderByDesc(['topRating' => Rating::select('rating')
    ->whereColumn('business_id', 'businesses.id')
    ->orderBy('rating', 'desc')
    ->limit(1)])
->get();

please make sure that to set the business table name instead of 'businesses'

more details about Subquery Selects in:

https://laravel.com/docs/7.x/eloquent#advanced-subqueries

edited May 28 '20 at 10:38

answered May 28 '20 at 08:36

OMR

11,736
5
20
35

There is a syntax error, the orderByDesc([ is not closed. Besides, is there a reason for the limit please? – mykoman May 28 '20 at 09:16
i corrected the syntax error. for the limit(1) sure it 's necessary .. because you don't want all the rating to be part of ordering ... just only the highest rating – OMR May 28 '20 at 10:40

score 0 · Answer 2 · answered May 28 '20 at 08:30

0

For this, you have to join manualy the table :

$business = Business::with('images')
    ->join('rating_table', 'rating_table.business_id', '=', 'business_table.id')
    ->where('isActive', true)
    ->where(function ($q) use ($query){
        $q->where('name', 'LIKE','%'.$query.'%')
            ->orWhere('description', 'LIKE','%'.$query.'%');
    })
    ->orderBy('rating_table.value', 'DESC')
    ->get();

After many tries, it's not possible to order the "parent" by their relationship using Eloquent, you can order inside the relationship, but that's it.

answered May 28 '20 at 08:30

Vincent Decaux

9,857
6
56
84

this get the result ordered by rating_table.value ... not by highest rating like answer wanted – OMR May 28 '20 at 09:13
It returns empty result even no matter the query string – mykoman May 28 '20 at 09:14

score 0 · Answer 3 · answered May 28 '20 at 08:35

0

You'll have to use a join I believe like so:

$business = Business::select([
    'businesses.*', \DB::raw('AVG(ratings.rating) as avg_rating')
  ])
  ->join('ratings', 'businesses.id', '=', 'ratings.business_id')
  ->orderBy('ratings.avg_rating', 'DESC')
  ->get();

Not tested

answered May 28 '20 at 08:35

Nasa

317
3
11

Column not found: 1054 Unknown column 'ratings.avg_rating' in 'order clause' (SQL: select `businesses`.*, AVG(ratings.rating) as avg_rating from `businesses` inner join `ratings` on `businesses`.`id` = `ratings`.`business_id` order by `ratings`.`avg_rating` desc) when i tried it – mykoman May 28 '20 at 09:03

Laravel order collection based on property of a one to many related relationship

3 Answers3