Поиск файлов определённого типа (запрос SQL)

Поиск файлов определённого типа (запрос SQL)

от Mikhail Apakin -
Количество ответов: 7

Здравствуйте! 

Затык на кажущейся простой проблеме.
Нужно сделать SQL-запрос, выдающий список курсов и количество к ним относящихся файлов определённого типа (например, 'ppt').
Пробовал два пути:

SELECT cou.id, cou.fullname, count(f.id) FROM prefix_files f
LEFT JOIN prefix_context con ON f.contextid=con.id
LEFT JOIN prefix_course_modules mod ON mod.id=con.instanceid
LEFT JOIN prefix_course cou ON mod.course=cou.id
WHERE f.filename LIKE '%ppt%'
GROUP BY cou.id

и

SELECT c.id AS CourseID,
c.fullname AS CourseName,
COUNT(
SELECT f.id FROM prefix_files f
LEFT JOIN prefix_context con ON f.contextid=con.id
LEFT JOIN prefix_course_modules mod ON con.instanceid=mod.id
LEFT JOIN prefix_course c ON mod.course=c.id
WHERE f.filename LIKE '%ppt%'
) AS PPT_Files
GROUP BY c.id

Оба варианта приводят к ошибке, которую никак не могу отловить. Подозреваю, что она проста и тупа, но не могу поймать.
Заранее спасибо.
Сумма оценок: -
В ответ на Mikhail Apakin

Re: Поиск файлов определённого типа (запрос SQL)

от Vadim Tabunshchik -
Изображение пользователя Developers

COUNT(SELECT - это неверная конструкция

И запросы некорректные, так как файлы прикрепляются не к курсу, а к конкретному модулю.

Попробуйте такой запрос:

SELECT
     c.fullname 'Курс'
    ,COUNT(c.id) 'Презентаций'
FROM {course} c
     JOIN {course_modules} cm ON c.id=cm.course
     JOIN {context} ctx ON ctx.instanceid = cm.id
WHERE ctx.id IN
     (SELECT f.contextid FROM
             {files} f
      WHERE f.filename LIKE '%.ppt' AND f.component LIKE 'mod_%'
      GROUP BY f.contextid)
GROUP BY c.fullname

Если фигурные скобки не подходят, поменяйте названия таблиц на prefix_

 

Сумма оценок: -
В ответ на Vadim Tabunshchik

Re: Поиск файлов определённого типа (запрос SQL)

от Mikhail Apakin -

Спасибо, но...

Вроде правильно всё, но считает неверно.

А почему COUNT(SELECT) неверно? Я хочу посчитать, сколько у меня файлов и делаю запрос по номеру курса.

Почему не работает вот такая конструкция?


SELECT c.id AS CourseID, c.fullname AS CourseName,
COUNT(
SELECT f.id FROM {files} f
LEFT JOIN {context} con ON f.contextid=con.id
LEFT JOIN {course_modules} mod ON con.instanceid=mod.id AND mod.course=c.id
WHERE f.filename LIKE '%ppt%'
) AS PPT_Files
FROM {course} c
GROUP BY c.id
Сумма оценок: -
В ответ на Mikhail Apakin

Re: Поиск файлов определённого типа (запрос SQL)

от Vadim Tabunshchik -
Изображение пользователя Developers 
Вроде правильно всё, но считает неверно.

Угу, считает неверно, так как не учел, что в одном модуле (контексте) может быть несколько ppt, а GROUP BY f.contextid тогда лишнее.

А почему COUNT(SELECT) неверно?

Потому что SQL такого не понимает улыбаюсь

Я хочу посчитать, сколько у меня файлов и делаю запрос по номеру курса.

У вас ppt используются в описании курса, секций и т. п., не только в ресурсах? Тогда совсем другой запрос нужен, сложнее, так как нужно считать отдельно РРТ курса и РРТ в модулях

 

Сумма оценок: -
В ответ на Vadim Tabunshchik

Re: Поиск файлов определённого типа (запрос SQL)

от Vadim Tabunshchik -
Изображение пользователя Developers 
Тогда совсем другой запрос нужен, сложнее, так как нужно считать отдельно РРТ курса и РРТ в модулях

Проверяйте:

SELECT
        qq.fullname 'Курс'
     ,SUM(qq.FID) 'Презентаций'
from (

    SELECT
        c.fullname,
        COUNT(f.id) FID
    FROM
        mdl_files f
        LEFT JOIN mdl_context ctx ON ctx.id = f.contextid AND ctx.contextlevel = 70
        LEFT JOIN mdl_course_modules cm ON cm.id = ctx.instanceid
        LEFT JOIN mdl_course c ON c.id = cm.course
    WHERE f.filename LIKE '%.ppt%' AND f.component LIKE 'mod_%'
    GROUP BY c.fullname

union all

    SELECT
        c.fullname,
        COUNT(f.id) FID
    FROM
        mdl_files f
        LEFT JOIN mdl_context ctx ON ctx.id = f.contextid AND ctx.contextlevel = 50
        LEFT JOIN mdl_course c ON c.id = ctx.instanceid
    WHERE f.filename LIKE '%.ppt%' AND f.component = 'course'
    GROUP BY c.fullname

      ) qq
GROUP BY
qq.fullname
Сумма оценок:2 (1)
В ответ на Vadim Tabunshchik

Re: Поиск файлов определённого типа (запрос SQL)

от Mikhail Apakin -

Я всё понял. Всё оказалось проще.

1) Почему оба ваши варианта показывали неправильные значения: надо было группировать не по названию курса, а по его айдишнику.
В разных направлениях были курсы с одинаковым названием, и он их суммировал.

2) Почему не работали мой самый первый вариант: оказывается, слово mod является функцией MySQL и соответственно использовать его в качестве алиаса названия таблицы было глупо. 

Итак, вот правильный вариант, который мне был нужен:


SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cou.id,'">',cou.id,'. ',cou.fullname,'</a>') AS Course
, count(f.id) 'PPT files' FROM {files} f
LEFT JOIN {context} con ON f.contextid=con.id
LEFT JOIN {course_modules} mls ON mls.id=con.instanceid
LEFT JOIN {course} cou ON mls.course=cou.id
WHERE f.filename LIKE '%ppt%' AND f.component = 'mod_resource'
GROUP BY cou.id
ORDER BY cou.fullname


Огромное вам спасибо за помощь!

Сумма оценок: -
В ответ на Mikhail Apakin

Re: Поиск файлов определённого типа (запрос SQL)

от Vadim Tabunshchik -
Изображение пользователя Developers
  1. Понятно. Предупреждать же нужно улыбаюсь
    Замените GROUP BY c.fullname на GROUP BY c.id и GROUP BY qq.fullname на GROUP BY qq.id, а также добавьте в SELECT c.fullname… ещё вывод c.id, чтоб ошибки не было. Получите подсчет файлов во всех модулях и областях курса.
  2. Посмотрите запросы в Мудл, там используются свои алиасы для таблиц: context - ctx, course - c, course_modules - cm и т .д. Тут не нужно придумывать что-то своё.
вот правильный вариант, который мне был нужен:

Ваш вариант считает файлы, находящиеся только в модуле resource. А если это «Страница» (mod_page), «Ссылка» (url), или файл прикреплен к секции, описанию курса, etc? Или их считать не нужно?

Пожалуйста подмигиваю

Сумма оценок: -
В ответ на Vadim Tabunshchik

Re: Поиск файлов определённого типа (запрос SQL)

от Karsinora Fenriz -
Спасибо вам огромное! Только вместо mdl_ написала prefix_ , потому что не работало.
Конечно, не очень удобно, что курсы только по названию и в список попадают в том числе и скрытые "архивные" курсы. Когда много одноименных курсов определить какой именно трудно. Но такую проблемку можно решить просто добавив, дополнительно ID курса и отсеять в выборке скрытые. 
Сумма оценок: -