Hola,
No encuentro la manera de sacar todos los mensajes internos de la plataforma, De todos los roles, origen, destino, mensaje, fecha y curso desde el cual se realizaron.
Consigo sacar toda la información excepto el curso correctamente.
SELECT
u.username AS 'From',
CONCAT(u.firstname ,' ',u.lastname) AS 'From Name',
u2.username AS 'To',
CONCAT(u2.firstname ,' ',u2.lastname) AS 'To Name',
DATE_FORMAT(FROM_UNIXTIME(me.timecreated), '%Y-%m-%d %H:%i') AS 'When',
me.subject AS 'Subject',
me.smallmessage AS 'Message',
c.fullname as 'Curso'
FROM lexeducamoodle.mdl_message me
JOIN lexeducamoodle.mdl_role_assignments AS ra ON ra.userid = me.useridfrom
JOIN lexeducamoodle.mdl_role_assignments AS ra2 ON ra2.userid = me.useridto
JOIN lexeducamoodle.mdl_context AS ctx ON ra.contextid = ctx.id AND ra2.contextid = ctx.id
JOIN lexeducamoodle.mdl_course AS c ON c.id = ctx.instanceid
JOIN lexeducamoodle.mdl_user u ON u.id = me.useridfrom
JOIN lexeducamoodle.mdl_user u2 ON u2.id = me.useridto
ORDER BY me.useridfrom, me.useridto, me.timecreated
Con esta consulta no me muestra todos los mensajes, los limita.
Alguien me podría echar un cable, me lo pide fundae.
2.8.5
Saludos y Gracias