Trouble sorting top-selling courses in the correct order

Courses
1

I’m having a problem with my code for getting the most popular courses. Here’s what I’ve done:

$popularCourses = DB::table('orders')
    ->select('course_id', DB::raw('COUNT(course_id) as purchase_count'))
    ->groupBy('course_id')
    ->orderBy('purchase_count', 'desc')
    ->get();

$topCourseIds = $popularCourses->pluck('course_id')->toArray();

$bestSellers = Course::whereIn('id', $topCourseIds)->get();

When I check $topCourseIds, it shows:

[
  0 => 4,
  1 => 1,
  2 => 2
]

This looks right because course 4 is the top seller, then course 1, and then course 2. But when I look at $bestSellers, the courses are in the wrong order: 1, 2, 4.

Why isn’t it keeping the right order? How can I fix this so the courses are listed from most popular to least popular?

2

The issue you’re encountering is due to Eloquent’s default behavior when using whereIn(). It doesn’t maintain the order of the IDs you provide.

To solve this, you can use the orderByRaw() method as suggested, but there’s also another approach using the sortBy() method on the collection:

$bestSellers = Course::whereIn('id', $topCourseIds)->get()
    ->sortBy(function ($course) use ($topCourseIds) {
        return array_search($course->id, $topCourseIds);
    });

This method allows you to sort the collection based on the position of each course’s ID in your $topCourseIds array, effectively preserving the order of popularity.

Remember to use values() at the end if you need an array instead of a collection:

$bestSellers = $bestSellers->values();

This approach is database-agnostic and might be more flexible if you ever switch database systems.

3

yo ethan, i’ve hit this snag before. quick fix: try using the sortBy() method on ur collection after fetching. it’s super easy:

$bestSellers = Course::whereIn(‘id’, $topCourseIds)->get()->sortBy(function($course) use ($topCourseIds) {
return array_search($course->id, $topCourseIds);
});

this’ll keep ur courses in the right order. lemme kno if it works for ya!

4

Hey Ethan85! I totally get your frustration with the sorting issue. Been there, done that! :sweat_smile:

Have you tried using the orderByRaw() method instead? It might help preserve the order you’re looking for. Something like this could work:

$bestSellers = Course::whereIn('id', $topCourseIds)
    ->orderByRaw("FIELD(id, " . implode(',', $topCourseIds) . ")")
    ->get();

This tells MySQL to sort based on the order of IDs in your $topCourseIds array. Pretty neat trick, right?

By the way, what kind of courses are you working with? I’m always curious about different e-learning platforms. Got any favorites among your top sellers?