How can I get the top 10 highest rated books?

@Tray2 I may have been more complicated in my ask (or just unclear) but this is more that what I was looking for.

I was really just looking for a simple ordering by average rating but also take into account ties and breaking them by number or ratings - and then also throwing out any books that don't have at least 5 ratings. In Eloquent.

However, you've taken it to a point of combining the rating and number or ratings into a ranking system or algorithm which is very helpful - at least as a start that I can begin tinkering with. I'll start playing with this soon and see if it works for my needs. Thank you.

@thebookcollector You are welcome.

You can add this to the query to rule out any with less than five ratings.

where  count(*) > 5

But that isn't really necessary with the way I wrote the query.

Doing something like that in eloquent usually gets a bit messy. I would suggest pushing it down into a view in the database and then you can just

$topBooks = BookRatingView::orderBy('score', 'DESC')->take(10)->get();

Here is a post on creating and using database views in Laravel.

https://tray2.se/posts/use-a-view-instead-of-a-complex-eloquent-query-in-your-laravel-application

@Tray2 - I found that using the DB view, I wasn't able to access relationships and eager loading, so I converted your first comment to eloquent and it's working great. Thank you for the help!

@thebookcollector Glad it worked out for you.

The point of a database view is to decrease the number of relationships, you can have them yes, but your case, I would just pull in the author, title and whatever else into the view.

Here is an example from a project that I'm working on when I have the time.

        DB::statement("CREATE OR REPLACE VIEW book_index_views AS
                    SELECT
                    (SELECT GROUP_CONCAT(a.id ORDER BY a.id SEPARATOR ',')
                    FROM authors a, author_book ab
                    WHERE a.id = ab.author_id
                    AND ab.book_id = b.id) author_id,
                    (SELECT GROUP_CONCAT(concat(a.last_name, ', ', a.first_name)
                    ORDER BY a.last_name, a.first_name SEPARATOR ' & ')
                    FROM authors a, author_book ab
                    WHERE ab.author_id = a.id
                    AND ab.book_id = b.id) author_name,
                    b.id book_id,
                    b.title,
                    b.part,
                    b.published_year,
                    CASE s.name
                        WHEN 'Standalone'
                        THEN b.published_year
                        ELSE (SELECT MIN(bi.published_year)
                              FROM books bi
                              WHERE bi.series_id = b.series_id)
                        END series_started,
                    f.name format,
                    g.name genre,
                    s.name series,
                    s.id series_id
                    FROM books b,
                         formats f,
                         genres g,
                         series s
                    WHERE b.genre_id = g.id
                    AND b.format_id = f.id
                    AND b.series_id = s.id
        ");

As you can see, I'm pulling in data from four different tables in my main query

1. books
2. formats
3. genres
4. series

And I also pull in the authors in a subquery.

That allows me to do this in my controller

   public function __invoke(Request $request)
    {
        return view('books.index')
            ->with([
                'books' => BookIndexView::query()
                   ->orderBy('author_name')
                    ->orderBy('series')
                    ->orderBy('part')
                    ->orderBy('published_year')
                    ->get(),
            ]);
    }

You can find the project here https://github.com/Tray2/mediabase