Поля для работы с группой

Вот мы то расписание преподавателей, то расписание аудиторий смотрим. А про себя любимых как будто бы и забыли.

Конечно не забыли, просто расписание групп самая сложная штука.

Сейчас все разберем =О

Для начала воспользуемся уже изученным способом вытаскивания расписания.

Если взглянуть на устройство таблицы schedule_items там есть целых три поля, которые относятся к группе это:

• groups – список групп в текстовом виде, для которых проводится занятие
• group_id – идентификатор группы для которой проводится занятие
• ngroup – номер подгруппы, для которой проводится занятие

давайте возьмем какую-нибудь группу. Опять же чтобы у всех было одно и тоже, я возьму какое-нибудь старое расписание. Возьму группу ИСТб-22-1, расписание за ноябрь 2022 https://www.istu.edu/schedule/?group=464447&date=2022-11-28

Получается ее идентификатор 464447. Напишем запрос, который вытащит расписание этой группы по полю group_id.

По аналогии как мы фильтровали у преподавателя и у аудитории, пишем:

SELECT day, para, everyweek, groups, discipline, *
FROM schedule_items
WHERE group_id = 464447 -- тут по группе фильтруем
     and dbeg = '2022-11-28'
ORDER BY day, para, everyweek

получается:

в принципе неплохо, давайте как в прошлом задании, оставим только один день, и расписание на четную неделю:

SELECT day, para, everyweek, groups, discipline, ngroup, id
FROM schedule_items
WHERE group_id = 464447
     and dbeg = '2022-11-28'
     and (
        (day = 9 and everyweek = 1 )
        or  (day = 2 and everyweek = 2)
     )
ORDER BY day, para, everyweek

получим что-то такое

сравним с данными на сайте:

так, вроде 3 строки смог сопоставить. Только лекция Математика не сходится…

Кстати, смотрите. У нас тут две Физики и может возникнуть вопрос как я понял какая к какой строке относится?

Дело в том, что у лабораторных как правило указан номер подгруппы, для которой это занятие проводится, а в таблице, это значение хранится как раз в поле ngroup. Сверяем и видим:

правда от этого лекция математики не появляется…

Разбираемся с лекцией

Ищем эту лекцию в БД

Давайте попробуем ее найти в БД. Для этого нам надо взглянуть на расписание преподавателя, который ведет это занятие. Тыкаем на имя преподавателя правой кнопкой

из полученной ссылки https://www.istu.edu/schedule/?prep=937 вытаскиваем id.

У меня получился 937 и пишем запрос который выдаст нам расписание этого преподавателя на этот же день. Я добавлю в SELECT поле group_id, чтобы понять, что за значение там находится:

SELECT day, para, everyweek, groups, group_id, discipline, ngroup, id
FROM schedule_items
WHERE teacher_id = 937
     and dbeg = '2022-11-28'
     and (
        (day = 9 and everyweek = 1 )
        or  (day = 2 and everyweek = 2)
     )
ORDER BY day, para, everyweek

смотрим результат:

во те на, это точно наша математика, но у нее поле group_id пустое!

Как же сайт у каких групп проводится пара?

Списки групп

И тут нам на помощь приходит вспомогательная таблица groups_lists. Она как раз хранит те самые списки для которых проводится занятие. Таблица имеет следующую структуру:

• id – уникальный идентификатор записи в таблице
• schedule_item_id – идентификатор занятия из таблицы schedule_items
• group_id – идентификатор группы для которой проводится занятие

попробуем сделать к ней запрос:

SELECT *
FROM groups_lists

увидим наборы циферок:

Встает вопрос, чего с этим всем делать? Давайте еще раз запустим наш запрос:

SELECT day, para, everyweek, groups, group_id, discipline, ngroup, id
FROM schedule_items
WHERE teacher_id = 937
     and dbeg = '2022-11-28'
     and (
        (day = 9 and everyweek = 1 )
        or  (day = 2 and everyweek = 2)
     )
ORDER BY day, para, everyweek

и посмотрим на вот это значение

это есть уникальный идентификатор занятие. Если сделать запрос к таблице groups_lists используя этот номер, то увидим как раз список идентификаторов групп, для которых проводится занятие. Пробуем:

SELECT *
FROM groups_lists
WHERE schedule_item_id = 5178226

увидим такое:

Давайте возьмем значения столбца group_id, чтобы проще было скопировать укажите выводить только один столбец:

SELECT group_id
FROM groups_lists
WHERE schedule_item_id = 5178226

и вытащим записи из таблицы groups:

SELECT *
FROM groups
WHERE id in (
    464436,
    464447,
    464448,
    466001,
    464774,
    464775,
    466123,
    464179
)

смотрим результат:

те самые группы с сайта:

Вложенный запрос

Давайте немного улучшим наш запрос. Смотрите, мы сейчас загнали список идентификаторов групп вручную. Вот тут:

WHERE id in (
    464436,
    464447,
    464448,
    466001,
    464774,
    464775,
    466123,
    464179
)

Но ведь запрос

SELECT group_id
FROM groups_lists
WHERE schedule_item_id = 5178226

как раз нам выдает этот самый список идентификаторов. А что если взять и вместо того чтобы загонять значения вручную, просто подставить запрос внутрь другого запроса. Вот так:

SELECT *
FROM groups
WHERE id in (
   SELECT group_id
   FROM groups_lists
   WHERE schedule_item_id = 5178226
)

то есть мы в скобочки просто вместо цифр подставили запрос. Проверим как работает:

точно так же, иногда только порядок записей может прыгать.

Так получается проще, чем писать два запроса отдельно, копировать значения, и все такое.

Но как это нам поможет достать лекции?

Запрашиваем занятия из списка групп

Сейчас будет, наверное, самое сложное для понимания. Вот у нас есть номер группы 464447 и есть список групп, привязанных к занятию

SELECT *
FROM groups_lists
WHERE schedule_item_id = 5178226

вот наша группа в этом списке

А что если взять и попробовать вместо фильтра по schedule_item_id = 5178226 сделать фильтр по нашей группе:

SELECT *
FROM groups_lists
WHERE group_id = 464447

увидим тьму записей. Давайте подумаем, что за значения лежат в schedule_item_id?

Это уникальные идентификаторы занятий, которые проводятся для нескольких групп среди которых фигурирует наша групп с номером 464447

Давайте попробуем написать запрос который вытащит все эти записи из schedule_items, пишем

SELECT *
FROM schedule_items
WHERE id in (
4543492,
4544201,
4544357,
4545577,
4545579,
4540414,
4540415,
4540418,
-- ОЙ ПОГОДИТЕ, ЧЕГО ЭТО МЫ ТВОРИМ

мы же выше делали подзапрос. Давайте тут так же сделаем. Сначала протестим его отдельно:

SELECT schedule_item_id
FROM groups_lists
WHERE group_id = 464447

обратите внимание что подзапрос всегда должен выдавать ровно один столбец, типа так:

и загоним его вместо списка идентификаторов

SELECT *
FROM schedule_items
WHERE id in (
    SELECT schedule_item_id
    FROM groups_lists
    WHERE group_id = 464447
)

запускаем:

смотрите, тут сплошняком занятия, которые проводятся для нескольких групп. Вот эти ИСТб-22, это тоже большие занятие, так как у ИСТб-22 три группы. И наша Математика тут тоже есть! =)

Объединяем запросы

Теперь давайте сделаем наш итоговый запрос который выдаст нам расписание группы на четную неделю. У нас был запрос

SELECT day, para, everyweek, groups, discipline, ngroup, id
FROM schedule_items
WHERE group_id = 464447
     and dbeg = '2022-11-28'
     and (
        (day = 9 and everyweek = 1 )
        or  (day = 2 and everyweek = 2)
     )
ORDER BY day, para, everyweek

Теперь надо к нему добавить условие

id in (
    SELECT schedule_item_id
    FROM groups_lists
    WHERE group_id = 464447
)

причем это условие должно срабатывать только если group_id пустой. Это можно написать примерно так:

group_id = 464447 OR id in (
    SELECT schedule_item_id
    FROM groups_lists
    WHERE group_id = 464447
)

то есть либо group_id равно 464447, либо если не равно (например, если пустое), то проверяем не подпадает ли идентификатор занятия в список занятий, которые проводятся для нескольких групп включая 464447.

Получится такое страшилище (главное в скобках не запутаться):

SELECT day, para, everyweek, groups, discipline, ngroup, id
FROM schedule_items
WHERE (
         group_id = 464447 OR id in (
            SELECT schedule_item_id
            FROM groups_lists
            WHERE group_id = 464447
         )
      ) 
      and dbeg = '2022-11-28'
      and (
         (day = 9 and everyweek = 1 )
         or  (day = 2 and everyweek = 2)
      )
ORDER BY para , everyweek

запускаем:

Красота! =)

Задание