Using the moodle API...get_records_sql method

Using the moodle API is good practice, this is the main page for it. Main moodle DB API

If you do use the API and in particular the get_records_sql() method, DON'T FORGET TO INCLUDE A UNIQUE COLUMN AS THE FIRST FIELD IN THE SELECT list.

Example 1 will return a very different record set than Example 2.

Example 1

    global $DB;
   
    return
        $DB->get_records_sql('
        SELECT 
            c.id AS courseid, 
            c.fullname, 
            u.firstname, 
            u.lastname
        FROM
            mdl_role_assignments ra,
            mdl_user u,
            mdl_course c,
            mdl_context cxt
        WHERE ra.userid = u.id
            AND ra.contextid = cxt.id
            AND cxt.contextlevel = 50
            AND cxt.instanceid = c.id
        ');

Example 2

    global $DB;
   
    return
        $DB->get_records_sql('
        SELECT 
            ra.id, 
            c.id AS courseid, 
            c.fullname, 
            u.firstname, 
            u.lastname
        FROM
            mdl_role_assignments ra,
            mdl_user u,
            mdl_course c,
            mdl_context cxt
        WHERE ra.userid = u.id
            AND ra.contextid = cxt.id
            AND cxt.contextlevel = 50
            AND cxt.instanceid = c.id
        ');

What is the difference? The ONLY difference is the first field in the SELECT list.  In Example 1, the first field is the courseid, which is a unique value in the table, but not in this result set.  In example 2, I added the id field from the role_assignments table. I do not need this field, the query does!  The first column is used as the array keys on the $records array - which is how the data structure is returned by $DB->get_records_sql().

Ommitting a unique value field as the first field in the select list has haunted me for a long time.  I would sit for hrs plugging SQL into mysql workbench and see a different result than plugging the same SQL into the $DB->get_records_sql() method  Eventually, I would punt in favor of calling the database using mysql_query(), rather than the $DB object.

Moral of the story?

If you do use the API and in particular the get_record_sql() method, DON'T FORGET TO INCLUDE A UNIQUE COLUMN AS THE FIRST FIELD IN THE SELECT list. 

Everything in hindsight is so obvious.