Using Eloquent subqueries to randomise grouped results

Grouping items in a database query and then selecting a random item from each group is surprisingly tricky. As with most things involving GROUP BY, it doesn’t work quite how you¹ might expect.

Let’s dig in.

The contrived scenario

Imagine you’re building a movie recommendation app. The films are stored in a movies table, which looks like this:

You want to provide your users with a random movie from each genre.

Figuring out the SQL query

Intuitively, it seems like this should do the trick²:

SELECT *
FROM movies
GROUP BY genre
ORDER BY RANDOM();

However, whilst this does randomise the order of the results, it always returns the same movie for each genre. In order to get a random movie per genre, we need to:

1. Randomise the list of movies.
2. Group the randomised list by genre.

In SQL-speak, we need to select from a randomised list, not directly from the movies table. That means we need a subquery:

SELECT *
FROM (
	SELECT *
	FROM movies
	ORDER BY RANDOM()
)
GROUP BY genre;

Figuring out the Eloquent query

Okay, we have our SQL query, but how do we recreate this query in Eloquent³?

For once, the Laravel documentation comes up short. It does mention subqueries, but that’s about it. Time to dig into the source.

Under the hood, Eloquent uses the Query/Builder class to build queries. We’ve established that we need to select from a subquery, and that’s exactly what the fromSub method does.

The method accepts our subquery⁴ and an alias⁵, which in this case could be an arbitrary string.

$subquery = Movie::query()->inRandomOrder();

Movie::query()
    ->fromSub($subquery, as: 'movies')
    ->groupBy('genre')
    ->get();

And with that, we have our list of recommended movies, with a random film from each genre.

Bonus round

There is one limitation to our solution: the genres always appear in the same order.

If you’d like to randomise the order of the genres, it’s much simpler to shuffle the returned collection:

Movie::query()
    ->fromSub($subquery, as: 'movies')
    ->groupBy('genre')
    ->get()
    ->shuffle();