3

i am trying to write a query to get the name and path of files of a course in Moodle but it seems like i am never be able to do it. Here is what i have done uptill now , any help with this is highly appreciated :)

SELECT SOURCE file,
              course,
              contenthash path
FROM mdl_files
INNER JOIN mdl_context ON mdl_files.contextid = mdl_context.id
JOIN mdl_resource ON instanceid = mdl_resource.id
WHERE course=
    (SELECT id
     FROM mdl_course
     WHERE fullname="XXXX")
Justin
  • 9,634
  • 6
  • 35
  • 47
chossen-addict
  • 370
  • 1
  • 7
  • 31

3 Answers3

6

There is no column called "file" in any of those Moodle tables. Are you actually referring to the "mdl_files.filename" column?

If so try this query:

SELECT
    mdl_files.source,
    mdl_files.filename,
    mdl_files.contenthash,
    mdl_context.path,
    mdl_resource.course,
    mdl_resource.name,
    mdl_course.fullname, 
    mdl_course.shortname
FROM
    mdl_files
INNER JOIN mdl_context ON mdl_files.contextid = mdl_context.id
INNER JOIN mdl_resource ON mdl_context.instanceid = mdl_resource.id
INNER JOIN mdl_course ON mdl_resource.course = mdl_course.id
WHERE (mdl_course.fullname = 'XXXX')

Is that what you want?

luisdev
  • 558
  • 7
  • 22
4

Try this.... just replace "58" below with the appropriate course ID number

SELECT cm.id, cm.course, cm.module, mdl.name AS type,
       CASE
            WHEN mf.name IS NOT NULL THEN mf.name
            WHEN mb.name IS NOT NULL THEN mb.name
            WHEN mr.name IS NOT NULL THEN mr.name
            WHEN mu.name IS NOT NULL THEN mu.name
            WHEN mq.name IS NOT NULL THEN mq.name
            WHEN mp.name IS NOT NULL THEN mp.name
            WHEN ml.name IS NOT NULL THEN ml.name
            ELSE NULL
       END AS activityname,
       CASE
            WHEN mf.name IS NOT NULL THEN CONCAT('/mod/forum/view.php?id=', cm.id)
            WHEN mb.name IS NOT NULL THEN CONCAT('/mod/book/view.php?id=', cm.id)
            WHEN mr.name IS NOT NULL THEN CONCAT('/mod/resource/view.php?id=', cm.id)
            WHEN mu.name IS NOT NULL THEN CONCAT('/mod/url/view.php?id=', cm.id)
            WHEN mq.name IS NOT NULL THEN CONCAT('/mod/quiz/view.php?id=', cm.id)
            WHEN mp.name IS NOT NULL THEN CONCAT('/mod/page/view.php?id=', cm.id)
            WHEN ml.name IS NOT NULL THEN CONCAT('/mod/lesson/view.php?id=', cm.id)
            ELSE NULL
       END AS linkurl, f.id AS fileid, f.filepath, f.filename, CONCAT('/filedir/', SUBSTRING(f.contenthash, 1, 2), '/', SUBSTRING(f.contenthash, 3, 2), '/', f.contenthash) AS filesystempath, f.userid AS fileuserid, f.filesize, f.mimetype, f.author AS fileauthor, f.timecreated, f.timemodified
FROM mdl_course_modules AS cm
INNER JOIN mdl_context AS ctx ON ctx.contextlevel = 70 AND ctx.instanceid = cm.id
INNER JOIN mdl_modules AS mdl ON cm.module = mdl.id
LEFT JOIN mdl_forum AS mf ON mdl.name = 'forum' AND cm.instance = mf.id
LEFT JOIN mdl_book AS mb ON mdl.name = 'book' AND cm.instance = mb.id
LEFT JOIN mdl_resource AS mr ON mdl.name = 'resource' AND cm.instance = mr.id
LEFT JOIN mdl_url AS mu ON mdl.name = 'url' AND cm.instance = mu.id
LEFT JOIN mdl_quiz AS mq ON mdl.name = 'quiz' AND cm.instance = mq.id
LEFT JOIN mdl_page AS mp ON mdl.name = 'page' AND cm.instance = mp.id
LEFT JOIN mdl_lesson AS ml ON mdl.name = 'lesson' AND cm.instance = ml.id
LEFT JOIN mdl_files AS f ON f.contextid = ctx.id
WHERE cm.course = 58
AND mdl.name = 'resource'
AND ((f.mimetype = 'application/pdf') OR (f.id IS NULL))

NOTE: originally inspired by Darko Miletić solution here: https://moodle.org/mod/forum/discuss.php?d=153059#p1166404

Gavin G
  • 856
  • 6
  • 6
0

For anyone looking for an answer in 2023. This query will return all files along with the course it belongs to.

select f.id as "file_id", f.filesize as "file_filesize", f.filename as "file_filename", c.id as "course_id", c.shortname as "course_shortname", c.fullname as "course_fullname" 
from m_files f 
inner join m_context ctx on ctx.id = f.contextid 
inner join m_course_modules cm on cm.id = ctx.instanceid 
inner join m_course c on c.id = cm.course 
where ctx.contextlevel = 70
noot
  • 103
  • 1
  • 11