Displays a table of resource popularity by cohort. Rendered in output columns showing the course category and subcategory, the resource type and name with the number of views and the date of last view per resource, as well as the sections and subsections where they appear.
SELECT
CONCAT(cm.id, '-', ch.id) as pkey,
cm.id,
c.shortname as cs,
c.fullname as cf,
cc1.name as cc1,
cc2.name as cc2,
cs1.name as cs1,
cs2.name as cs2,
ch.name as ch,
m.name as rtype,
CASE WHEN m.name = 'resource' THEN r.name
WHEN m.name = 'page' THEN p.name
WHEN m.name = 'url' THEN ur.name
WHEN m.name = 'quiz' THEN q.name
WHEN m.name = 'workshop' THEN w.name
WHEN m.name = 'folder' THEN f.name ELSE 'N.C.' END as rname,
CASE WHEN ltci.itemoptional = 0 THEN 'Obligatoire' ELSE 'Facultatif' END as cmst,
COUNT(DISTINCT ltcc.userid) as dusers,
COUNT(*) as viewes,
MAX(l.timecreated) as lastview
FROM
{modules} m,
{learningtimecheck_item} ltci,
{learningtimecheck_check} ltcc,
{course} c,
{course_categories} cc1,
{course_categories} cc2,
{logstore_standard_log} l,
{cohort} ch,
{cohort_members} chm,
{course_modules} cm
LEFT JOIN
{resource} r
ON
r.id = cm.instance
LEFT JOIN
{url} ur
ON
ur.id = cm.instance
LEFT JOIN
{folder} f
ON
f.id = cm.instance
LEFT JOIN
{quiz} q
ON
q.id = cm.instance
LEFT JOIN
{page} p
ON
p.id = cm.instance
LEFT JOIN
{workshop} w
ON
w.id = cm.instance
LEFT JOIN
{course_sections} cs1
ON
cm.section = cs1.id
LEFT JOIN
{course_format_options} cfo
ON
cs1.id = cfo.sectionid
LEFT JOIN
{course_sections} cs2
ON
cs2.section = cfo.value AND cfo.courseid = cs2.course
WHERE
m.id = cm.module AND
m.name IN('resource', 'page', 'url', 'folder', 'quiz') AND
ltcc.item = ltci.id AND
ltci.moduleid = cm.id AND
ltcc.userid = chm.userid AND
cm.course = c.id AND
cc1.id = c.category AND
cc2.id = cc1.parent AND
ch.id = chm.cohortid AND
ltcc.userid = l.userid AND
l.objecttable = m.name AND
l.objectid = cm.instance AND
l.action = 'viewed'
<%%FILTERS%%>
GROUP BY
CONCAT(cm.id, '-', ch.id)
Tzo4OiJzdGRDbGFzcyI6OTg6e3M6NToidGl0bGUiO3M6NDQ6IlBvcHVsYXJpdMOpIGRlcyByZXNzb3VyY2VzIGRhbnMgbGVzIGNvaG9ydGVzIjtzOjEzOiJpbmJsb2NrbGF5b3V0IjtzOjE6IjAiO3M6NjoidGFyZ2V0IjtzOjY6Im1vb2RsZSI7czo4OiJzaG93ZGF0YSI7czoxOiIxIjtzOjk6InNob3dncmFwaCI7czoxOiIwIjtzOjExOiJzaG93bnVtc3VtcyI7czoxOiIxIjtzOjk6InNob3dxdWVyeSI7czoxOiIwIjtzOjE3OiJzaG93ZmlsdGVycXVlcmllcyI7czoxOiIwIjtzOjU6InF1ZXJ5IjtzOjIwMzU6IlNFTEVDVA0KICAgQ09OQ0FUKGNtLmlkLCAnLScsIGNoLmlkKSBhcyBwa2V5LA0KICAgY20uaWQsDQogICBjLnNob3J0bmFtZSBhcyBjcywNCiAgIGMuZnVsbG5hbWUgYXMgY2YsDQogICBjYzEubmFtZSBhcyBjYzEsDQogICBjYzIubmFtZSBhcyBjYzIsDQogICBjczEubmFtZSBhcyBjczEsDQogICBjczIubmFtZSBhcyBjczIsDQogICBjaC5uYW1lIGFzIGNoLA0KICAgbS5uYW1lIGFzIHJ0eXBlLA0KICAgQ0FTRSBXSEVOIG0ubmFtZSA9ICdyZXNvdXJjZScgVEhFTiByLm5hbWUNCiAgICAgICAgICAgICAgV0hFTiBtLm5hbWUgPSAncGFnZScgVEhFTiBwLm5hbWUNCiAgICAgICAgICAgICAgV0hFTiBtLm5hbWUgPSAndXJsJyBUSEVOIHVyLm5hbWUNCiAgICAgICAgICAgICAgV0hFTiBtLm5hbWUgPSAncXVpeicgVEhFTiBxLm5hbWUNCiAgICAgICAgICAgICAgV0hFTiBtLm5hbWUgPSAnd29ya3Nob3AnIFRIRU4gdy5uYW1lDQogICAgICAgICAgICAgIFdIRU4gbS5uYW1lID0gJ2ZvbGRlcicgVEhFTiBmLm5hbWUgRUxTRSAnTi5DLicgRU5EIGFzIHJuYW1lLA0KICAgQ0FTRSBXSEVOIGx0Y2kuaXRlbW9wdGlvbmFsID0gMCBUSEVOICdPYmxpZ2F0b2lyZScgRUxTRSAnRmFjdWx0YXRpZicgRU5EIGFzIGNtc3QsDQogICBDT1VOVChESVNUSU5DVCBsdGNjLnVzZXJpZCkgYXMgZHVzZXJzLA0KICAgQ09VTlQoKikgYXMgdmlld2VzLA0KICAgTUFYKGwudGltZWNyZWF0ZWQpIGFzIGxhc3R2aWV3DQpGUk9NDQogICB7bW9kdWxlc30gbSwNCiAgIHtsZWFybmluZ3RpbWVjaGVja19pdGVtfSBsdGNpLA0KICAge2xlYXJuaW5ndGltZWNoZWNrX2NoZWNrfSBsdGNjLA0KICAge2NvdXJzZX0gYywNCiAgIHtjb3Vyc2VfY2F0ZWdvcmllc30gY2MxLA0KICAge2NvdXJzZV9jYXRlZ29yaWVzfSBjYzIsDQogICB7bG9nc3RvcmVfc3RhbmRhcmRfbG9nfSBsLA0KICAge2NvaG9ydH0gY2gsDQogICB7Y29ob3J0X21lbWJlcnN9IGNobSwNCiAgIHtjb3Vyc2VfbW9kdWxlc30gY20NCkxFRlQgSk9JTg0KICAge3Jlc291cmNlfSByDQpPTg0KICAgci5pZCA9IGNtLmluc3RhbmNlDQpMRUZUIEpPSU4NCiAgIHt1cmx9IHVyDQpPTg0KICAgdXIuaWQgPSBjbS5pbnN0YW5jZQ0KTEVGVCBKT0lODQogICB7Zm9sZGVyfSBmDQpPTg0KICAgZi5pZCA9IGNtLmluc3RhbmNlDQpMRUZUIEpPSU4NCiAgIHtxdWl6fSBxDQpPTg0KICAgcS5pZCA9IGNtLmluc3RhbmNlDQpMRUZUIEpPSU4NCiAgIHtwYWdlfSBwDQpPTg0KICAgcC5pZCA9IGNtLmluc3RhbmNlDQpMRUZUIEpPSU4NCiAgIHt3b3Jrc2hvcH0gdw0KT04NCiAgIHcuaWQgPSBjbS5pbnN0YW5jZQ0KTEVGVCBKT0lODQogICB7Y291cnNlX3NlY3Rpb25zfSBjczENCk9ODQogICBjbS5zZWN0aW9uID0gY3MxLmlkDQpMRUZUIEpPSU4NCiAgICB7Y291cnNlX2Zvcm1hdF9vcHRpb25zfSBjZm8NCk9ODQogICAgY3MxLmlkID0gY2ZvLnNlY3Rpb25pZA0KTEVGVCBKT0lODQogICB7Y291cnNlX3NlY3Rpb25zfSAgY3MyDQpPTg0KICAgY3MyLnNlY3Rpb24gPSBjZm8udmFsdWUgQU5EDQogICBjZm8uY291cnNlaWQgPSBjczIuY291cnNlDQpXSEVSRQ0KICAgbS5pZCA9IGNtLm1vZHVsZSBBTkQNCiAgIG0ubmFtZSBJTigncmVzb3VyY2UnLCAncGFnZScsICd1cmwnLCAnZm9sZGVyJywgJ3F1aXonKSBBTkQNCiAgIGx0Y2MuaXRlbSA9IGx0Y2kuaWQgQU5EDQogICBsdGNpLm1vZHVsZWlkID0gY20uaWQgQU5EDQogICBsdGNjLnVzZXJpZCA9IGNobS51c2VyaWQgQU5EDQogICBjbS5jb3Vyc2UgPSBjLmlkIEFORA0KICAgY2MxLmlkID0gYy5jYXRlZ29yeSBBTkQNCiAgIGNjMi5pZCA9IGNjMS5wYXJlbnQgQU5EDQogICBjaC5pZCA9IGNobS5jb2hvcnRpZCBBTkQNCiAgIGx0Y2MudXNlcmlkID0gbC51c2VyaWQgQU5EDQogICBsLm9iamVjdHRhYmxlID0gbS5uYW1lIEFORA0KICAgbC5vYmplY3RpZCA9IGNtLmluc3RhbmNlIEFORA0KICAgbC5hY3Rpb24gPSAndmlld2VkJyAgDQogICA8JSVGSUxURVJTJSU+DQpHUk9VUCBCWQ0KICAgIENPTkNBVChjbS5pZCwgJy0nLCBjaC5pZCkiO3M6MTI6InNxbHBhcmFtdmFyMSI7czowOiIiO3M6MTQ6InNxbHBhcmFtbGFiZWwxIjtzOjA6IiI7czoxMzoic3FscGFyYW10eXBlMSI7czo2OiJjaG9pY2UiO3M6MTU6InNxbHBhcmFtdmFsdWVzMSI7czowOiIiO3M6MTI6InNxbHBhcmFtdmFyMiI7czowOiIiO3M6MTQ6InNxbHBhcmFtbGFiZWwyIjtzOjA6IiI7czoxMzoic3FscGFyYW10eXBlMiI7czo2OiJjaG9pY2UiO3M6MTU6InNxbHBhcmFtdmFsdWVzMiI7czowOiIiO3M6MTI6InNxbHBhcmFtdmFyMyI7czowOiIiO3M6MTQ6InNxbHBhcmFtbGFiZWwzIjtzOjA6IiI7czoxMzoic3FscGFyYW10eXBlMyI7czo2OiJjaG9pY2UiO3M6MTU6InNxbHBhcmFtdmFsdWVzMyI7czowOiIiO3M6MTI6InNxbHBhcmFtdmFyNCI7czowOiIiO3M6MTQ6InNxbHBhcmFtbGFiZWw0IjtzOjA6IiI7czoxMzoic3FscGFyYW10eXBlNCI7czo2OiJjaG9pY2UiO3M6MTU6InNxbHBhcmFtdmFsdWVzNCI7czowOiIiO3M6MTI6Im91dHB1dGZpZWxkcyI7czo1NjoiY2MyO2NjMTtjcztjZjtjczI7Y3MxO3J0eXBlO3JuYW1lO2R1c2Vyczt2aWV3ZXM7bGFzdHZpZXciO3M6MTM6Im91dHB1dGZvcm1hdHMiO3M6MzI6IiVzOyVzOyVzOyVzOyVzOyVzOyVzOyVzOyVkOyVkOyVEIjtzOjg6InBhZ2VzaXplIjtzOjA6IiI7czoxMDoiY2FjaGluZ3R0bCI7czowOiIiO3M6MTI6ImNsZWFuZGlzcGxheSI7czoxOiIxIjtzOjIyOiJjbGVhbmRpc3BsYXl1cHRvY29sdW1uIjtzOjE6IjIiO3M6ODoic29ydGFibGUiO3M6MToiMSI7czoxNToic3BsaXRzdW1zb25zb3J0IjtzOjA6IiI7czoxMToiZmllbGRsYWJlbHMiO3M6MTE1OiJDYXTDqWdvcmllO1NvdXMtY2F0w6lnb3JpZTtDb2RlO0NvdXJzO1NlY3Rpb247c291cy1zZWN0aW9uO1R5cGU7UmVzc291cmNlO1V0aWxpc2F0ZXVycyBkaXN0aW5jdHM7VnVlcztEZXJuacOocmUgdnVlIjtzOjk6InRhYmxldHlwZSI7czo2OiJsaW5lYXIiO3M6NzoiZmlsdGVycyI7czoxMzoiY2gubmFtZSBhcyBjaCI7czoxMjoiZmlsdGVybGFiZWxzIjtzOjc6IkNvaG9ydGUiO3M6MTQ6ImZpbHRlcmRlZmF1bHRzIjtzOjU6IkZJUlNUIjtzOjEzOiJmaWx0ZXJvcHRpb25zIjtzOjI6InN4IjtzOjEyOiJ2ZXJ0aWNhbGtleXMiO3M6MDoiIjtzOjg6Imhvcml6a2V5IjtzOjA6IiI7czoxNToidmVydGljYWxmb3JtYXRzIjtzOjA6IiI7czoxMToiaG9yaXpmb3JtYXQiO3M6MDoiIjtzOjc6InNwbGl0b24iO3M6MDoiIjtzOjE0OiJ2ZXJ0aWNhbGxhYmVscyI7czowOiIiO3M6MTA6Imhvcml6bGFiZWwiO3M6MDoiIjtzOjExOiJwYXJlbnRzZXJpZSI7czowOiIiO3M6MTA6InRyZWVvdXRwdXQiO3M6MDoiIjtzOjE3OiJ0cmVlb3V0cHV0Zm9ybWF0cyI7czowOiIiO3M6MTA6InhheGlzZmllbGQiO3M6MDoiIjtzOjEwOiJ4YXhpc2xhYmVsIjtzOjA6IiI7czo3OiJ5c2VyaWVzIjtzOjA6IiI7czoxNDoieXNlcmllc2Zvcm1hdHMiO3M6MDoiIjtzOjQ6InltaW4iO3M6MDoiIjtzOjQ6InltYXgiO3M6MDoiIjtzOjExOiJ0aWNrc3BhY2luZyI7czowOiIiO3M6MTI6InNlcmllc2xhYmVscyI7czowOiIiO3M6MTA6InlheGlzbGFiZWwiO3M6MDoiIjtzOjk6ImdyYXBodHlwZSI7czo0OiJsaW5lIjtzOjEwOiJncmFwaHdpZHRoIjtzOjM6IjQ1MCI7czoxMToiZ3JhcGhoZWlnaHQiO3M6MzoiMjUwIjtzOjEwOiJzaG93bGVnZW5kIjtzOjE6IjAiO3M6NzoibWFwdHlwZSI7czo3OiJST0FETUFQIjtzOjQ6Inpvb20iO3M6MToiNiI7czoxMDoiZGF0YXRpdGxlcyI7czowOiIiO3M6MTM6ImRhdGFsb2NhdGlvbnMiO3M6MDoiIjtzOjk6ImRhdGF0eXBlcyI7czowOiIiO3M6MzoibGF0IjtzOjE6IjAiO3M6MzoibG5nIjtzOjE6IjAiO3M6MTM6InNob3dsb3dlcmJhbmQiO3M6MToiMSI7czoxMzoidXBwZXJiYW5kdW5pdCI7czo1OiJNT05USCI7czoxMzoibG93ZXJiYW5kdW5pdCI7czo0OiJZRUFSIjtzOjE4OiJ0aW1lbGluZWV2ZW50dGl0bGUiO3M6MDoiIjtzOjE4OiJ0aW1lbGluZWV2ZW50c3RhcnQiO3M6MDoiIjtzOjE2OiJ0aW1lbGluZWV2ZW50ZW5kIjtzOjA6IiI7czoxNzoidGltZWxpbmVldmVudGxpbmsiO3M6MDoiIjtzOjE3OiJ0aW1lbGluZWV2ZW50ZGVzYyI7czowOiIiO3M6MTQ6InRpbWVsaW5lY29sb3JzIjtzOjA6IiI7czoxODoidGltZWxpbmVjb2xvcmZpZWxkIjtzOjA6IiI7czoxNzoidGltZWxpbmVjb2xvcmtleXMiO3M6MDoiIjtzOjc6Im51bXN1bXMiO3M6MDoiIjtzOjEzOiJudW1zdW1mb3JtYXRzIjtzOjA6IiI7czoxMjoibnVtc3VtbGFiZWxzIjtzOjA6IiI7czo4OiJjcm9ubW9kZSI7czo5OiJub3JlZnJlc2giO3M6ODoiY3JvbmhvdXIiO3M6MDoiIjtzOjc6ImNyb25taW4iO3M6MDoiIjtzOjEzOiJjcm9uZnJlcXVlbmN5IjtzOjU6ImRhaWx5IjtzOjg6Im1ha2VmaWxlIjtzOjE6IjAiO3M6MTA6ImZpbGVvdXRwdXQiO3M6MDoiIjtzOjE3OiJmaWxlb3V0cHV0Zm9ybWF0cyI7czowOiIiO3M6MTA6ImZpbGVmb3JtYXQiO3M6MzoiQ1NWIjtzOjE1OiJmaWxlc3Fsb3V0dGFibGUiO3M6MDoiIjtzOjEyOiJmaWxlbG9jYXRpb24iO3M6MDoiIjtzOjIxOiJmaWxlcGF0aGFkbWlub3ZlcnJpZGUiO3M6MDoiIjtzOjg6InNhdmV2aWV3IjtzOjIzOiJFbnJlZ2lzdHJlciBldCBhZmZpY2hlciI7czo5OiJzcWxwYXJhbXMiO047czo5OiJpc3J1bm5pbmciO2k6MDtzOjg6Imxhc3Rjcm9uIjtpOjA7fQ==
Back to generic query catalogue index - Component index - Back to plugins index - Back to the catalogue index