Ayuda con una SQL (filtrar por suspendido en el curso)

Ayuda con una SQL (filtrar por suspendido en el curso)

de Juan Carlos Jimenez -
Número de respuestas: 2

Hola a tod@s. Tengo muy pocas nociones (o casi ninguna) sobre Bases de datos.

Tengo montado un moodle versión 3.8

Pero necesito realizar una consulta que me muestre solo los usuarios activos de un curso en concreto, pero no consigo averiguar qué campo utilizar para filtrar (no me refiero a si el usuario está activo en moodle).

Espero que podáis ayudarme

Esta es la query que tengo desarrollada:

SELECT
     DISTINCT u.username,
     u.firstname AS NOMBRE,
     u.lastname  AS APELLIDOS,
     ic.shortname AS CURSO,
     IF(ic.groupmode IS NULL, "PRESENCIAL", "ONLINE") AS GRUPO,
     u.email,
     from_unixtime(u.firstaccess) as "ALUMNO DESDE",
     miscampos.data AS "TELEFONO"
FROM
     mdlul_course ic
     JOIN mdlul_context con ON con.instanceid = ic.id
     JOIN mdlul_role_assignments ra ON con.id = ra.contextid AND con.contextlevel = 50
     JOIN mdlul_role r ON ra.roleid = r.id
     JOIN mdlul_user u ON u.id = ra.userid
     JOIN mdlul_user_info_data miscampos ON miscampos.userid = u.id AND miscampos.fieldid= 3
     LEFT JOIN mdlul_groups_members gm ON u.id = gm.userid
     LEFT JOIN mdlul_groups g ON g.id = gm.groupid
WHERE
     ic.id=12
     AND g.name IS NULL
     AND u.username NOT LIKE 'email@domain.com'
     AND ic.groupmode <> '0'
     AND r.id NOT IN (3,4)
ORDER BY g.name ASC

Promedio de valoraciones: -
En respuesta a Juan Carlos Jimenez

Re: Ayuda con una SQL (filtrar por suspendido en el curso)

de Sandra Piedrabuena -
Hola Juan Carlos:
A ver si entiendo. 
Necesitas seleccionar los estudiantes suspendidos en cada curso.
El usuario está suspendido cuando en la columna prefix_user.suspended es igual a uno (1)
SELECT q.fullname, u.username, u.firstname, u.lastname
FROM mdl_user as u
INNER JOIN mdl_role_assignments as ra ON u.id = ra.userid
INNER JOIN mdl_role as r ON r.id = ra.roleid
INNER JOIN mdl_context as c ON ra.contextid = c.id
INNER JOIN mdl_course as q ON c.instanceid = q.id
WHERE  ra.id = 5 and u.suspended = 1
GROUP BY q.fullname, u.username, u.firstname, u.lastname

Aquí tienes varios queries que te pueden servir de base:
https://docs.moodle.org/310/en/ad-hoc_contributed_reports
Saludos
Promedio de valoraciones: -
En respuesta a Sandra Piedrabuena

Re: Ayuda con una SQL (filtrar por suspendido en el curso)

de Juan Carlos Jimenez -
Hola. Gracias por contestar.
En Moodle un usuario puede estar suspendido a 2 niveles:
  • a nivel global: el campo es "suspended" y se ubica en la tabla user
  • a nivel de curso: el campo es "status" y se ubica en la tabla user_enrolments
Y al final mirando la estructura de la bd de Moodle he conseguido con esta query (la dejo por si alguien le interesa)

SELECT
DISTINCT u.username,
u.firstname AS NOMBRE,
u.lastname AS APELLIDOS,
ic.shortname AS CURSO,
u.email,
from_unixtime(u.firstaccess) as "ALUMNO DESDE",
miscampos.data AS "TELEFONO"
FROM
prefix_course ic
JOIN prefix_context con ON con.instanceid = ic.id
JOIN prefix_role_assignments ra ON con.id = ra.contextid AND con.contextlevel = 50
JOIN prefix_role r ON ra.roleid = r.id
JOIN prefix_user u ON u.id = ra.userid
JOIN prefix_user_enrolments ue ON ue.userid = ra.userid
JOIN prefix_user_info_data miscampos ON miscampos.userid = u.id AND miscampos.fieldid= 3
LEFT JOIN prefix_groups_members gm ON u.id = gm.userid
LEFT JOIN prefix_groups g ON g.id = gm.groupid
WHERE
ic.id=12 /* id del curso */
AND ue.status = 0 /* usuario en el curso ACTIVO */
AND ue.enrolid = 40 /* id de tipo de matriculación */
AND r.id = 5 /* rol estudiante */
ORDER BY APELLIDOS ASC

Promedio de valoraciones:Útil (1)