Hello, I'm putting together a report on the activities of teachers to be issued at the end of a program, using the plugin Configurable reports.
I need to include in the report the number of private messages sent by the teacher using moodle internal messaging.
Does anyone know if this could be achieved utlizando any SQL statement, or other means?
Moodle 2.3.1+ (Build: 20120831)
Best regards!
I could be wrong, but as far as I know, messages aren't course-specific, so there isn't any way to determine how many messages a teacher sent from within a particular course. You could, however, count the total number of messages a teacher has sent from all his courses combined, between certain dates. It would look something like this:
SELECT u.lastname, COUNT(m.fullmessage)
FROM mdl_message m
JOIN mdl_user u ON u.id=m.useridfrom
WHERE m.timecreated > UNIX_TIMESTAMP('2012-01-01')
AND m.timecreated < UNIX_TIMESTAMP('2012-06-30')
GROUP BY u.lastname
HTH,
Kimber
Hello Kimber, thanks a lot for your prompt reply.
When i tried to use your script i got a message "no specific prefix".
Then i replaced on the script mdl_ with prefix_ (read this in other forum, dont know if it is correct), but when running the report i got a message "no records found".
SELECT u.lastname, COUNT(m.fullmessage)
FROM prefix_message m
JOIN prefix_user u ON u.id=m.useridfrom
WHERE m.timecreated > UNIX_TIMESTAMP('2013-01-01')
AND m.timecreated < UNIX_TIMESTAMP('2013-01-20')
GROUP BY u.lastname
Again thanks a lot for your time.
Best regards!
David.
Oh yes, sorry about the "mdl_" ...I was using MySQL Workbench and have to use "mdl_" instead of "prefix_". You're right, in Configurable Reports, you should use prefix_.
Is it possible that no one has sent a message since January 1? When I run that identical script, I get a list of users and a count of their messages, as expected. Have you tried broadening the date filter to include more time?
By the way, this script will count messages sent by all users, not just teachers. To filter only for users with the teacher role, you'll need to join a couple more tables. If you need help with that, we can cross that bridge when we come to it.
yes the script works, but in my localhost has a random behavior, with every new message sent it changes the info on the report, sometimes shows 3 records, other 2 records and so.
could you be so kind to help me with the join to get the messages sent from teachers and non editing teachers?
I'm really gratefull for your help, thanks a lot.
Best regards!
David.
I'm copying this from a more complex SQL query I wrote, so there's probably a more efficient way to write this for your purposes, but try:
SELECT u.lastname, COUNT(m.fullmessage)
FROM prefix_message m
JOIN prefix_user u ON u.id=m.useridfrom
JOIN prefix_user_enrolments ue ON ue.userid=u.id
JOIN prefix_enrol e ON e.id=ue.enrolid
JOIN prefix_course c ON c.id=e.courseid
JOIN prefix_context AS ctx ON ctx.instanceid = c.id
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_role AS r ON r.id = e.roleid
WHERE m.timecreated > UNIX_TIMESTAMP('2013-01-01')
AND m.timecreated < UNIX_TIMESTAMP('2013-01-20')
AND ra.userid=u.id
AND ctx.instanceid=c.id
AND ra.roleid='3' ### "3"=teacher, "4="non-editing teacher", "5" = student
AND c.visible='1' ### "1" for course visible (active), "0" for hidden
GROUP BY u.lastname
Be sure to actually test your results for accuracy, since I haven't checked, myself.
Kimber
Thanks a lot kimber for sharing this query, it works ok.
Doing a little research i have found the reason for the random results i mentioned above, it seems that the table prefix_message stores the "unread" messages, and the table prefix_message_read stores the "readed" messages.
What i need is to show the total number for read and unread messages.
I have been trying to achieve this, but my SQL knowledge is very limited (although you are helping me a lot).
Could you point me in the right direction with this issue?
Best regards!