Level 1
This seemed to do the trick:
->leftJoin('ratings', function ($join) {
$join->on('ratings.book_id', '=', 'book.id')->where('ratings.user_id', '=', $this->user->id);
})
->orderBy('ratings.value', 'desc')
Jan 30, 2023
8
Level 1
How to order by relationship
Edit: Stand by...I think I figured it out. Will update shortly.
I got some very good help here (https://laracasts.com/discuss/channels/eloquent/id-like-to-return-all-books-in-a-users-collection-with-all-the-users-ratings) that is working great. The new thing that I've been struggling with for a couple hours is now ordering by that relationship field.
So, users can add books to their collection. They can then go to their user page and view their whole collection, along with all of the ratings they have added to books. That all works great. Now, I'd like to let them order by their own ratings. Problem is, the order by is taking into account all user's ratings. So if User1 rated 'The Great Gatsby' with a 7, and 'Invisible Man' a 7.5 and User2 rated 'The Great Gatsby' with an 8 - When User1 goes to view their collection and sort by ratings, it's going to put 'The Great Gatsby' above 'Invisible Man'.
Not sure how I can order by only the given user's ratings and not by all ratings in the table. This is what I have:
Book::with([
'images',
'ratings' => function ($query) {
$query->where('user_id', $this->user_id);
}])
->join('collections as c', 'c.book_id', '=', 'books.id')
->where('c.user_id', '=', $this->user->id)
->leftJoin('ratings', 'ratings.book_id', '=', 'books.id')
->orderBy('ratings.value', 'desc')
->paginate(20)
Also tried...
Book::with([
'images',
'ratings' => function ($query) {
$query->where('user_id', $this->user_id);
}])
->join('collections as c', 'c.book_id', '=', 'books.id')
->where('c.user_id', '=', $this->user->id)
->leftJoin('ratings', 'ratings.book_id', '=', 'books.id', 'and', 'ratings.user_id', '=', $this->user->id)
->orderBy('ratings.value', 'desc')
->paginate(20)
Level 11
@thebookcollector maybe it works for you, but I think it can be done better.
First, why you're doing with('ratings') and ALSO doing leftJoin('ratings') later? Those may be in conflict.
Now, following my article on ordering by relationship, I suggest three options:
Option 1. Callback Function with orderBy
Book::with(['ratings' => function($query) {
$query->orderBy('ratings.value', 'desc');
}])->get();
Option 2. Model: ALWAYS Order By Field X
class Book extends Model
{
public function ratings()
{
return $this->hasMany(Rating::class)->orderBy('value', 'desc');
}
}
Option 3. Separate Ordered Relationship
class Book extends Model
{
public function ratings()
{
return $this->hasMany(Rating::class);
}
public function ratingsByValue()
{
return $this->hasMany(Rating::class)->orderBy('value', 'desc');
}
}
Then, Controller:
Book::with('ratingsByValue')->get();
1 like
Level 102
@PovilasKorop I think @thebookcollector wants to reorder the main query, not the relationship :)
1 like
Level 11
@Sinnbeck hmm, I have re-read the post and not sure at this point. Order the books by their ratings which are a relationship. Still stand by my advice :) But might be wrong here, the author knows best.
1 like
Level 1
@PovilasKorop and @sinnbeck - it seems like both of you are saying the same thing. Yes, I want to reorder the main query, along with the relationship column. I'd like to sort books in a user's collection by the user's rating for each book. eg.
In Search of Lost Time 9.3
Ulysses 9.1
Don Quixote 8.9
One Hundred Years of Solitude 8.3
The Great Gatsby 8.0
Moby Dick 7.9
War and Peace 7.8
To answer your question @PovilasKorop about having the with('ratings') and also the leftJoin, it's because when I only had the "with" I wasn't able to order by 'ratings.value' - so I added the leftJoin. I'm sure there's a better way and I'll definitely be looking into your suggestions and trying those out.
Also of note - the query is actually more involved than what I've posted about here. There's actually a live search involved, ordering by other columns, show/hide columns and books based on user selections, and including several other relationships similar to ratings that users can choose to sort by. I just simplified the query for the post so I'm not giving too much unneeded info where people would then need to try to understand a bunch of different tables. Made it as straightforward as I could to get to the issue I was having.
Level 102
@thebookcollector I would probably sort by a sub query. Found a quick example here https://stackoverflow.com/a/53111908
Level 8
Learn Eloquent, save the head ache.
Level 1
@Randy_Johnson - I'm trying. I have the relationships set up in the appropriate models and using "with()" for the relationship. Did lots of searching and found some good resources but none that were working for my specific situation (may have been my error). I'm definitely enjoying the learning journey though. If you have a more specific suggestion to try (that wasn't already suggested by PovilasKorop) I'd love to try it out.
Please or to participate in this conversation.