Coming to grips with database functions (2.3.2)

Coming to grips with database functions (2.3.2)

by Richard Crawford -
Number of replies: 6

I'm trying to pull up a list of users who are enrolled as students in any course throughout our site. Ideally, since I only need to know whether a user is enrolled in any course and not how many courses they're enrolled in, I'd use the DISTINCT keyword, and my query would look like this:

SELECT DISTINCT(userid) FROM {role_assignments} WHERE roleid = 5

How do I translate that query into something Moodle's internal database functions will understand? I tried:

$users = $DB->execute("SELECT DISTINCT(userid) FROM {role_assignments} WHERE roleid = 5");

but that only gives me a Boolean value.

I've studied /lib/dml/moodle_database.php until my eyes fell out of my head but I haven't figured this out.

Any help would be greatly appreciated.

Average of ratings: -
In reply to Richard Crawford

Re: Coming to grips with database functions (2.3.2)

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

There are several things wrong here.

1. Your SQL makes no sense. It does not answer the question you say you want the answer to.

2. $DB->execute is definitely the wrong method to be using. Given what you are trying to do, $DB->record_exists might be right.

3. But really, you should not be using low-level DB api when higher-level APIs are available. For seeing whether users are enrolled in a given course, you should be using the enrolment API.

4. More generally, if you want to learn how to do something in Moodle, and can't find it in the developer docs (http://docs.moodle.org/dev/Main_Page ), then find some other part of Moodle that does the same thing, and then look at the code behind it to see how it works. In this case, go into a course, and bring up the participants list. Look at the URL, that tells you which PHP file to start reading the code of.

Average of ratings:Useful (1)
In reply to Tim Hunt

Re: Coming to grips with database functions (2.3.2)

by Richard Crawford -

Sorry, I suppose I wasn't being entirely clear with my question.

I need a list of ALL the users in the site who are enrolled in ANY course whatsoever. For example, if John is enrolled in Beginning Squash and Jane is enrolled in Basic Spanglish, then my query needs to return both John and Jane, regardless of the fact that they are enrolled in entirely different courses. Furthermore, I only want one row returned for each of them. The query:

select userid from {role_assignments} where roleid = 5

gives me duplicate roles. I don't want that.

I hope that, given this explanation, the reasoning behind my query makes more sense. Again, I need the information on all users who are enrolled in any courses on a sitewide level, not merely a course level.

Obviously, $DB->execute() is not what I need. I said that. My preference is always to use the higher-level APIs when possible. You're quite right in that examining the source code for pages such as user/index.php would give me the starting point I would need if I needed a list of students in a *given course*, but I could not find an equivalent function to find a list of users who are students in any course throughout the site. If that functionality exists in Moodle, I have not found it.

ETA: I should add that the results of this query will inform a larger process where we purge our site of all users who are not instructors or program administrators or admin users. Since we have well over three thousand students and close to one hundred courses, doing this by hand is not feasible.

Average of ratings: -
In reply to Richard Crawford

Re: Coming to grips with database functions (2.3.2)

by Darko Miletić -
Picture of Core developers Picture of Plugin developers 
select id, userid from {role_assignments} where roleid = 5 group by userid

 

$records = $DB->get_records_sql($sql);
foreach ($records as $record) {
    echo $record->userid, PHP_EOL;
}
Average of ratings: -
In reply to Darko Miletić

Re: Coming to grips with database functions (2.3.2)

by Darko Miletić -
Picture of Core developers Picture of Plugin developers

And you should avoid hardcoding role ids. That can change etc.

Average of ratings: -
In reply to Darko Miletić

Re: Coming to grips with database functions (2.3.2)

by Richard Crawford -

I generally do. In this case, I confirmed that 5 was the student role before I set about doing this.

Average of ratings: -