Get Records SQL giving incorrect result

If you have code that uses $DB->get_records_sql() and it is only giving you one row, or an incorrect number of rows, then first just make sure you are using the plural form get_records_sql() and not get_record_sql().

If that's not the issue, the other 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 probably want to use the unique id from mdl_user_enrolments.

NOTE: in this example, using user id isn't a great choice either because a user can have more than one enrolment.
No Comments
Back to top