php - Laravel order collection based on property of a one to many related relationship
Get the solution ↓↓↓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
Answer
Solution:
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.
Answer
Solution:
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
Answer
Solution:
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:
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: cannot set properties of undefined (setting '_dt_cellindex')
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.