Hola a tod@s, tengo un problema con un informe en SQL, que se agrava porque mis conocimientos de este lenguajes son mínimos.
Estoy intentado realizar un informe de interactividad entre el profesor y el alumno en un curso determinado.
Las primeras consultas creo que están bien, el problema viene en:
.- Respuestas del alumno al profesor, que creo que hay un error y en los mensajes:
.- Mensajes del alumno al profesor y del profesor al alumno.
os paso el código que tengo puesto, por si alguíen me puede echar una mano.
## ALL posts IN course so far
# COUNT posts BY student
, COUNT(DISTINCT fps.id) AS 'Mensajes del alumno en el foro'
# COUNT posts BY student
, COUNT(DISTINCT fpi.id) AS 'Mensajes del Profesor en el foro'
# COUNT replies TO student posts BY instructors
, COUNT(DISTINCT fpi.id) AS 'Respuestas del profesor'
# USING link back TO student posts ON replies, GET UNIQUE student IDs responded
, COUNT(DISTINCT fpsr.id) - COUNT(DISTINCT fpi.id) AS 'Respuestas del alumno'
# Messages BETWEEN students AND instructors - TO DATE
,
(SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE
mfs.useridfrom = allstu.id AND mfs.useridto = instr.id ) AS 'Mensajes
del alumno al profesor'
, (SELECT COUNT(DISTINCT mts.id) FROM
prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto =
allstu.id) AS 'Mensajes del profesor al alumno'
## JOINS
# START BY getting ALL the students IN the course
FROM prefix_user AS allstu
JOIN prefix_role_assignments AS ras ON allstu.id = ras.userid AND ras.roleid = 5
JOIN prefix_context AS ctx ON ras.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
JOIN prefix_course_categories AS cc ON c.category = cc.id
# Now we GET the forums AND forum discussions FROM this course ONLY
LEFT JOIN prefix_forum AS frm ON frm.course = c.id AND c.id = %%COURSEID%%
LEFT JOIN prefix_forum_discussions AS fd ON fd.course = %%COURSEID%% AND fd.forum = frm.id
# These are forum discussion posts just BY students WITHIN specified TIME
LEFT JOIN prefix_forum_posts AS fps ON fps.userid = allstu.id AND fps.discussion = fd.id
# Separately, we CONNECT the instructors OF the courses
# We can USE the context we have already gotten FOR the students
LEFT JOIN prefix_role_assignments AS rai ON rai.contextid = ctx.id
LEFT JOIN prefix_user AS instr ON instr.id = rai.userid AND rai.roleid =3
# Now we will CONNECT TO posts BY instructors that are replies TO student posts
# This IS a LEFT JOIN, because we don't want to eliminate any students from the list
LEFT JOIN prefix_forum_posts AS fpi ON fpi.discussion = fd.id AND fpi.userid = instr.id AND fpi.parent = fps.id
# To get identities of only those students who were replied to:
# Connect from instr replies back up to parent posts by students again
# This has to be a LEFT JOIN, we know these posts exist but don't eliminate non-responded students
LEFT JOIN prefix_forum_posts AS fpir ON fpir.id = fpi.parent
# We also want TO know IF students are replying TO one another
# These are posts that are replies TO student posts
# Again, a LEFT JOIN
LEFT JOIN prefix_forum_posts AS fpsr ON fpsr.discussion = fd.id AND fpsr.parent = fps.id
# GET the activity modules
LEFT JOIN prefix_course_modules AS cm ON c.id = cm.course
# GET the assignments
LEFT JOIN prefix_assign AS a ON cm.instance = a.id
LEFT JOIN prefix_assign_submission AS asb ON a.id = asb.assignment AND asb.userid=allstu.id
LEFT JOIN prefix_assign_grades AS asg ON asg.assignment = a.id AND asg.userid = allstu.id AND asg.assignment = asb.assignment
# We care about messages that involve BOTH the instructor AND students OF this course
# messages FROM instructor TO students:
# LEFT JOIN prefix_message AS mts ON mts.useridfrom = instr.id AND mts.useridto = allstu.id
# LEFT JOIN prefix_message AS mfs ON mfs.useridfrom = instr.id AND mfs.useridto = allstu.id
WHERE
c.id = %%COURSEID%%
# GROUP BY c.shortname , allstu.id
GROUP BY allstu.id
ORDER BY allstu.lastname