The course_assignments table allows duplicate instructor_id values because one instructor can be assigned to multiple courses.
I need to get a list of instructors along with all the courses they’re assigned to. Right now I’m using this query:
SELECT instructor_id, GROUP_CONCAT(DISTINCT course_id SEPARATOR ',') AS courses, [other fields]
FROM instructors LEFT JOIN course_assignments USING(instructor_id)
GROUP BY instructor_id;
This gives me course IDs as a comma-separated string for each instructor. Then in PHP I use explode() to convert it into an array of course IDs. It works but I’m wondering if there’s a better approach?
The only alternative I can think of is splitting this into two separate queries - one for instructor data and another for the course assignments.
Update: I can’t just remove the GROUP BY and handle duplicate rows because I’m selecting other large data fields which would create too much overhead.
Note: This is a simplified version of my actual queries to keep things clear.
Your GROUP_CONCAT approach is solid for this. I’ve handled similar cases and string concatenation is usually more efficient than people think, especially when you’re already pulling other large fields. If you’re on MySQL 5.7.22+, try JSON_ARRAYAGG instead of GROUP_CONCAT. You’ll get a proper JSON array that PHP handles natively with json_decode() - cleaner than using explode(). Here’s the syntax: sql SELECT instructor_id, JSON_ARRAYAGG(DISTINCT course_id) AS courses, [other fields] FROM instructors LEFT JOIN course_assignments USING(instructor_id) GROUP BY instructor_id; This performs well in production and cuts out the string manipulation in your app code. The two-query approach works but adds complexity and extra database trips.
This approach works great in production - I use it all the time. But watch out for memory usage on the database side. When you’ve got instructors with tons of courses, GROUP_CONCAT can blow up your result sets and tank query performance. I usually throw a SUBSTRING limit on the concatenated results to keep things manageable. If someone needs more courses, just run a second query.
Another trick: paginate at the instructor level instead of trying to grab everything at once. You keep your single-query structure but avoid memory spikes when traffic gets heavy. The two-query approach is solid too, especially if you’re dealing with lots of reads and can cache properly.
Both approaches work, but you’re missing the biggest piece - automation. Why manually build queries and parse data every single time?
I hit this same problem building instructor dashboards. The real game changer? Automate the entire pipeline instead of just tweaking queries.
Build a workflow that pulls instructor data, processes course assignments, and formats everything your app needs. No more GROUP_CONCAT limits or JSON_ARRAYAGG headaches.
The workflow handles database calls, transforms data, and caches results. Trigger it when course assignments change - fresh data without constant polling.
I built this for our learning management system. Handles thousands of instructor records with course lists, formats everything perfectly, delivers to multiple endpoints. Way cleaner than cramming this logic into application code.
Latenode makes database automation ridiculously simple. Set up the whole pipeline visually and you’re done.
your current approach is solid - i’ve def seen worse! if performance becomes an issue, try using prepared statements with your two-query method. grab the instructors first, then batch the course assignments with where instructor_id IN (?). split queries often outperform complex joins on large datasets anyway.
Your current solution works great for most cases. I’ve handled similar instructor-course setups for years and GROUP_CONCAT does the job reliably. The performance difference between your single query and splitting it into two queries doesn’t matter unless you’re working with massive datasets. One thing to check - bump up your group_concat_max_len value if you haven’t. I hit truncation issues once when instructors had tons of course assignments. The explode() overhead in PHP is nothing compared to multiple network round trips. Stick with what works until you actually hit performance issues.