How can I fetch top-selling courses?

Courses
1

I’m facing an issue retrieving top-selling courses. My query returns course IDs in descending order but the resulting course objects don’t match that order. Help?

$queryResults = DB::table('sales')
    ->select('course_code', DB::raw('COUNT(course_code) as total'))
    ->groupBy('course_code')
    ->orderBy('total', 'desc')
    ->get();

$idArray = [];
foreach ($queryResults as $record) {
    $idArray[] = $record->course_code;
}

$popularCourses = Course::whereIn('id', $idArray)->get();

// Debug output
print_r($idArray);
2

Based on my experience, the problem stems from the whereIn clause not preserving the order of the IDs. I addressed a similar issue by ordering the results explicitly in the query. After fetching the IDs in the order you require, you can use something like orderByRaw using MySQL’s FIELD() function. For example, Course::whereIn(‘id’, $idArray)->orderByRaw(‘FIELD(id, ’ . implode(’,', $idArray) . ‘)’)->get(); This approach ensures that the order of the returned course objects exactly matches the order determined by your sales query.

3

hey, i had a simlar issue. i solved it by reordering after the query. i used a sort function that compared each courses id index in the id array from sales. its a post-fetch fix which worked well enough. give it a try, might help!

4

I encountered a similar situation where the ordering was off due to reliance on the whereIn clause. One solution I adopted was to adjust the ordering after retrieving the courses. I fetched the courses and then applied a custom sort function that compares each course id against the original sorted id array from the sales query. This method gave me precise control over the final list order. Implementing this in PHP allowed me to maintain the specific ordering, even if the database query did not support it directly.

5

Hey Ethan85, I’ve been in similar situations where the ordering gets messed up post-fetch. One idea I played around with was actually to change the approach slightly by doing a join instead of fetching IDs and then reassigning order in PHP. So, instead of running two separate queries, I combined the sales info directly into the course query. For instance, you can do something like:

$popularCourses = Course::join('sales', 'courses.id', '=', 'sales.course_code')
    ->select('courses.*', DB::raw('COUNT(sales.course_code) as total'))
    ->groupBy('courses.id')
    ->orderBy('total', 'desc')
    ->get();

This way, the returned course objects are already in the right order because the ordering is handled by the SQL query itself. I’m curious though, have you tried a join approach before, or did you prefer keeping the logic separate as in your original code? Would love to hear your take on it or any other alternative methods you’ve come across! :blush:

6

hey ethan, try a join query so sql orders the courses directly. i had a similar issue and merging the sales and courses tables fixed it. sorting then sits naturally in the db without extra php mess. good luck!