Trouble sorting top-selling courses correctly

Courses
1

Help needed with sorting popular courses

I’m working on a feature to show our most popular courses. But I’m running into a problem with the order. Here’s what I’ve done so far:

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

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

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

When I check $topCourseIds, it looks good:

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

This makes sense. Course 4 is the best seller, then 1, then 2.

But when I look at $bestSellers, the order is wrong. It shows course 1, then 2, then 4.

Why is this happening? How can I make sure the courses stay in the right order? Any ideas?

2

Hey there, fellow course enthusiast! :blush:

I see you’re wrestling with those tricky course rankings. Isn’t it frustrating when the data doesn’t line up quite right? I’ve been there too!

Have you considered using Laravel’s collection methods to solve this? They can be super handy in situations like this. Here’s a thought:

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

This way, you’re grabbing all the courses first, then sorting them based on their position in your original $topCourseIds array. It’s a bit different from the database-level solutions, but it might give you more flexibility.

What do you think about this approach? Have you tried anything similar? I’m curious to hear how it goes if you give it a shot!

Also, I’m wondering - how many courses are we talking about here? Is performance a big concern for you? It might affect which solution works best in your case.

3

hey there! i’ve run into this before. the problem is that whereIn doesn’t preserve the order of your ids. try using FIND_IN_SET in mysql or a custom ordering in laravel. something like:

$bestSellers = Course::whereIn(‘id’, $topCourseIds)
->orderByRaw(DB::raw("FIELD(id, " . implode(‘,’, $topCourseIds) . “)”))
->get();

That should fix it for ya!

4

I encountered a similar issue in one of my projects. The solution lies in Laravel’s query ordering. When you use whereIn(), it doesn’t maintain the original order of your IDs. To fix this, you can leverage Laravel’s orderByRaw() method with a custom SQL FIELD() function.

Here’s what worked for me:

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

This approach tells the database to sort the results based on the order of IDs in your $topCourseIds array. It should resolve your ordering problem and maintain the correct sequence of your best-selling courses.

Remember to test this thoroughly, especially if you’re working with a large dataset, as it might impact query performance.