$DB->get_record_sql only returning a single row

So you’ve written a query, passed it to $DB->get_records_sql() and, what’s this – only one row? What’s going on, where’s the rest of the query data?

First off, make sure you are using the plural form (get_records_sql() and not get_record_sql()).

If that’s not the issue then read on.

The reason this can happen is that Moodle uses the first column in your query as a unique id and then filters results down to that id field. However, if your first column isn’t unique across your data set, you won’t get every row.

For example, your query may select user enrolments for a course. But if you put your course id as the first column, you’ll only get one row, even if there are 30 enrolments in that course, because all of those enrolments belong to that one course id and that is the unique identifier used.

The fix is simple, find (or if you need to, create) a unique id so that the first column is unique for every row in your query. In the user enrolments example you might use the user ID or the user_enrolments ID.