Estoy trabajando en Moodle 3.1
Deseo generar una consulta (query) para desplegar las tareas pendientes que tiene cada alumno en sus cursos tambien si hay publicaciones nuevas en los foros.
¿Cuáles son las tablas que debería considerar?
estoy intentando con las tablas user, assignment, course_modules
Gracias de antemano
>>>>>>>>>>>>>>>>>> tengo este query, muestra las tareas en un curso , la cantidad de tareas subidas por los alumnos y la cantidad de tareas revisadas por el docente, debes de fijarte que uso la categoría de curso (campo c.category) también puedes sustituirlo por el id de curso o el shortname de curso
select a.shortname, a.fullname, a.iteminstance,a.itemname, a.calificados, b.tareassubidas, c.retroalimentacion, d.totalalumnos
from
(
SELECT c.shortname, c.fullname ,gi.iteminstance,gi.itemname , count(distinct(gg.userid)) as calificados
FROM mdl_grade_items gi
left join mdl_grade_grades gg on gi.id = gg.itemid
left join mdl_course c on gi.courseid = c.id
where c.category = '197' and gi.itemmodule in ('assign')
and gg.finalgrade >= 0
group by c.fullname ,gi.itemname
-- ('assignment','assign')
) a
left join
(
SELECT c.shortname, a.id , a.name , count((asub.userid)) as tareassubidas
FROM mdl_assign a
left join mdl_assign_submission asub on a.id = asub.assignment
left join mdl_course c on a.course = c.id
where c.category = '197'
group by c.shortname, a.id , a.name
) b on a.shortname = b.shortname and a.iteminstance = b.id
left join
(
SELECT c.shortname, c.fullname ,gi.iteminstance,gi.itemname , count(gg.feedback) as retroalimentacion
FROM mdl_grade_items gi
left join mdl_grade_grades gg on gi.id = gg.itemid
left join mdl_course c on gi.courseid = c.id
where c.category = '197' and gi.itemmodule in ('assign')
and gg.finalgrade >= 0 and gg.feedback > " "
group by c.fullname ,gi.itemname
)c on c.shortname = a.shortname and a.iteminstance = c.iteminstance
left join
(
select c.shortname , count(u.username) as totalalumnos
FROM mdl_user u
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid
INNER JOIN mdl_course c ON c.id = ct.instanceid
INNER JOIN mdl_role r ON r.id = ra.roleid
WHERE r.id = 9 and c.category = '197'
group by c.shortname
)d on d.shortname = a.shortname
group by a.shortname, a.fullname, a.iteminstance,a.itemname
limit 0,50000
>>>>>>>>>>>>>>> también tengo este query, que muestra la cantidad de foros en un curso, la cantidad de participaciones en el foro, la cantidad de participaciones revisadas por el docente y la cantidad de participaciones hecha por el docente
SELECT a.shortname, a.fullname, a.iteminstance, a.itemname, a.calificados, b.postparticipacion, c.alumnospartiparon,d.docenteparticipacion, e.totalalumnos
FROM (
SELECT c.shortname, c.fullname ,gi.iteminstance,gi.itemname , count(distinct(gg.userid)) as calificados
FROM mdl_grade_items gi
left join mdl_grade_grades gg on gi.id = gg.itemid
left join mdl_course c on gi.courseid = c.id
where c.category = '197' and gi.itemmodule = 'forum'
and gg.finalgrade > 0
group by c.fullname ,gi.itemname ) a
Left join
(
select
c.shortname, f.id, f.name, count((p.userid)) as postparticipacion
from mdl_forum f
left join mdl_forum_discussions fd on f.id = fd.forum
left join mdl_forum_posts p on p.discussion = fd.id
left join mdl_course c on c.id = f.course
where c.category = '197'
group by c.shortname, f.name
) b on a.shortname = b.shortname and a.iteminstance = b.id
left join
(
select
c.shortname, f.id, f.name, count(distinct(p.userid)) as alumnospartiparon
from mdl_forum f
left join mdl_forum_discussions fd on f.id = fd.forum
left join mdl_forum_posts p on p.discussion = fd.id
left join mdl_course c on c.id = f.course
where c.category = '197'
group by c.shortname, f.name
)c on a.shortname = c.shortname and a.iteminstance = c.id
left join
(
select
c.shortname, f.id, f.name, count((p.userid)) as docenteparticipacion
from mdl_forum f
left join mdl_forum_discussions fd on f.id = fd.forum
left join mdl_forum_posts p on p.discussion = fd.id
left join mdl_user u on u.id = p.userid
left JOIN mdl_role_assignments ra ON ra.userid = u.id
left JOIN mdl_context ct ON ct.id = ra.contextid
left join mdl_course c on c.id = f.course and c.id = ct.instanceid
left JOIN mdl_role r ON r.id = ra.roleid
where c.category = '197' and r.id = 5
group by c.shortname, f.name
) d on a.shortname = d.shortname and a.iteminstance = d.id
left join
(
select c.shortname , count(u.username) as totalalumnos
FROM mdl_user u
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid
INNER JOIN mdl_course c ON c.id = ct.instanceid
INNER JOIN mdl_role r ON r.id = ra.roleid
WHERE r.id = 9 and c.category = '197'
group by c.shortname
) e on a.shortname = e.shortname
group by a.shortname, a.fullname, a.itemname
