Trouble sorting popular courses in database query

Courses
1

I’m having issues with a database query to find the most popular courses. Here’s what I’ve tried:

$popular = DB::table('purchases')->select('course_id', DB::raw('COUNT(course_id) as total'))
    ->groupBy('course_id')->orderBy('total', 'desc')->get();

$course_list = [];

foreach($popular as $item) {
    $course_list[] = $item->course_id;
}

$top_courses = Course::whereIn('id', $course_list)->get();

When I check $course_list, it shows:

[
  0 => 7,
  1 => 3,
  2 => 5
]

This looks right because course 7 is the top seller, then 3, then 5. But when I look at $top_courses, they’re in the wrong order: 3, 5, 7. Why is this happening and how can I fix it? I want the courses to stay in the order of most sales.

2

I’ve encountered a similar issue in my projects. The problem lies in how SQL handles the ‘whereIn’ clause. It doesn’t preserve the order of the input array.

One effective solution is to use the ‘orderByRaw’ method with a CASE statement. Here’s an example:

$top_courses = Course::whereIn('id', $course_list)
    ->orderByRaw("CASE id "
        . collect($course_list)->map(function ($id, $index) {
            return "WHEN {$id} THEN {$index}";
        })->implode(' ')
        . " END")
    ->get();

This approach maintains the original order of your $course_list. It’s more flexible than using FIELD() as it works across different database systems.

Remember to optimize your queries if you’re dealing with a large number of courses. Consider paginating results or limiting the number of top courses you fetch to improve performance.

3

hey owen, i’ve dealt w/ this before. the problem is whereIn doesn’t keep order. try using a subquery with ROW_NUMBER() to preserve the ranking:

$top_courses = DB::table('courses')
    ->joinSub($popular, 'p', function ($join) {
        $join->on('courses.id', '=', 'p.course_id');
    })
    ->orderBy('p.total', 'desc')
    ->get();

this should keep ur courses in the right order. lmk if it works!

4

Hey there, Owen_Galaxy! :slightly_smiling_face:

That’s an interesting problem you’ve got there. I’ve run into similar issues before when working with database queries and collections.

Have you considered using the whereIn method with a custom order? Something like this might work:

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

This tells the database to order the results based on the order of IDs in your $course_list. It’s a neat trick I learned a while back!

What do you think? Have you tried anything like this before? I’m curious to hear if it solves your issue or if you’ve found another workaround.

Also, I’m wondering - how many courses are you typically dealing with in your database? Is performance a concern with this query? Sometimes with larger datasets, we might need to approach things differently.

Let me know how it goes! Always happy to brainstorm more if needed. :blush: