General developer forum

Perhaps I should give some precisions to what we've come up to.

This is our SQL request :

SELECT DISTINCT
    u.username,
    c.fullname as "Course name",
    u.email,
    ra.id
   
From
    moodledev.mdl_user u,
    moodledev.mdl_course c,
    moodledev.mdl_enrol e,
    moodledev.mdl_role_assignments ra,
    moodledev.mdl_user_enrolments ue
Where
    u.id =ue.userid and
    ue.userid=ra.userid and
    ra.roleid= 3 and

    ue.enrolid= e.id and
    e.courseid= c.id
    group by ra.id

;

And we get a list of :

-teacher names

-course names related to the courses they have enrolled in

-their e-mail

-the unique ID (taken from the "mdl_role_assignments" table) that refers to a line that makes a recap of the users role each time it has been changed (if what I say makes sense to you)

--------------------------

The problem is that I have the same lines appearing when a user has been assigned several times to the same role. Considering I am selecting all the theachers from the database, I get :

Is there a way to select only the line with the highest ID ?

The probem we have is that some teachers are still listed as "teachers" in courses in which they arent enrolled anymore, because moodle keeps track of the role changes in the database. ("mdl_role_assignments" table)

Hello,

I am updating this topic to give more precisions on our progression.

The following diagram shows the links we figured out there was between he tables, that enable us to link a user to its courses :

Sorry if it looks like a class diagram, but I thought I'd be an easy solution to build it up quickly. Note that the class attributes actually coresspond to the names of the rows of the specified tables.

______________________________________

Somehow, by linking all of these table, we get the right amount of teachers, except that the course names aren't being updated.

Here's our new SQL request :

SELECT  DISTINCT ra.id as raid, u.username, u.email, r.name, c.fullname


FROM    moodledev.mdl_role_assignments ra,
        moodledev.mdl_user u,
        moodledev.mdl_role r,
        moodledev.mdl_course c,
        moodledev.mdl_enrol e,
        moodledev.mdl_user_enrolments ue
        
        
WHERE   u.id = ra.userid
AND     r.id = ra.roleid
AND     ra.roleid = 3
AND     ue.userid=ra.userid
AND     ue.enrolid= e.id
AND     e.courseid= c.id
     
GROUP BY raid

The only problem with this request is that when a teacher is part of more than 1 course, request returns the same source name twice instead of returning two lines with different course names. We get this :

instead of that :

And the first column is the role_assignement id.