Как правильно составить запрос для вывода тех, кто прошёл курс полностью?

Question

Есть 2 таблицы lesson:

   id  |       id_course  |  lesson_title
    1  |          3       |      Урок 1
    2  |          3       |      Урок 2
    3  |          3       |      Урок 3
    4  |          4       |      Урок 4
    5  |          4       |      Урок 5

history:

  id  |       id_lesson  |             email      |    date
  43  |          1       |     123@gmail.com      |    2018-07-09 13:17:05
  44  |          2       |     123@gmail.com      |    2018-07-09 13:18:05
  45  |          3       |     123@gmail.com      |    2018-07-09 13:19:05

Суть в чём, есть курсы, в них есть уроки.

Разные пользователи проходят урок -> записывается история о том что он прошёл урок.

Нужно сделать вывод людей, тех, кто полностью прошёл весь курс (т.е. все уроки в курсе).

В каждом курсе свой вывод прошедших людей.

Вот мой запрос, но не то:

SELECT
  h.*
FROM
  history h
  LEFT JOIN lesson l ON h.id_lesson = l.id
WHERE
  l.id_course= '$id'

Мне нужно чтобы выводило просто запись с email и датой, кто и когда закончил весь курс.

По логике, как я думаю, нужно сверить кол-во записей в history с id уроков каждого пользователя и кол-во самих уроков в таблице lesson по их id конкретного курса ->

И потом, если эти кол-ва записей совпадают, то выводить их email.

Как правильно составить запрос?

function total_score($id){
    global $connection;
   $query = "SELECT l.id_course, h.email, MAX(h.date) FROM lesson l LEFT JOIN history h ON h.id_lesson = l.id WHERE l.id_course= '$id' GROUP BY l.id_course, h.email HAVING SUM(h.id_lesson IS NULL) = 0";
    $res = mysqli_query($connection, $query);
    $total_score = array();
    while($row = mysqli_fetch_assoc($res)){
        $total_score[$row['email']] = $row;
    }
    return $total_score;
}

Исправил, но теперь выводит пользователя, если он хотя бы один урок прошёл, а не все три, как в этом примере.

Дату какую выводить? Последнего урока пользователя? И что такое "пользователь"? Как отличить одного пользователя от другого? — Anton Shchyrov, Jul 09 '18 at 13:43
да, последнего урока. Пользователь отличается по email — user8719712, Jul 09 '18 at 13:49
https://ru.stackoverflow.com/questions/423439/mysql-fetch-array-expects-parameter-1-to-be-resource-or-mysqli-result-boole — Anton Shchyrov, Jul 09 '18 at 15:03

Akina · Answer 1 · 2018-07-09T13:45:15.673

2

Для всех курсов - где-то так:

SELECT l.id_course, h.email, MAX(h.date)
FROM lesson l
LEFT JOIN history h ON h.id_lesson = l.id
GROUP BY l.id_course, h.email
HAVING SUM(h.id_lesson IS NULL) = 0

Для одного заданного курса - добавить WHERE.

edited Jul 09 '18 at 13:45

answered Jul 09 '18 at 13:43

Akina

31,807

Спасибо, сейчас потестю – user8719712 Jul 09 '18 at 13:49
Исправил, но теперь выводит пользователя, если он хотя бы один урок прошёл, а не все три, как в этом примере. – user8719712 Jul 09 '18 at 15:30

2SRTVF · Accepted Answer · 2018-07-10T04:57:46.073

Набор запросов с последовательным приближением к результату

create table `lesson`
(
  `id` int,
  `id_course` int,
  `lesson_title` text
);

create table history 
(
  `id` int,
  `id_lesson` int,
  `email` text,
  `date` date
);

Insert into `lesson` (`id`,`id_course`,`lesson_title`) values 
(1, 3, 'Урок 1'),
(2, 3, 'Урок 2'),
(3, 3, 'Урок 3'),
(4, 4, 'Урок 4'),
(5, 4, 'Урок 5')
;

Insert into `history` (`id`,`id_lesson`,`email`,`date`) values 
(43, 1, '100@gmail.com', '2018-07-10'),
(44, 2, '100@gmail.com', '2018-07-11'),
(45, 3, '100@gmail.com', '2018-07-12'),
(46, 4, '110@gmail.com', '2018-07-13'),
(47, 5, '110@gmail.com', '2018-07-14'),
(48, 1, '200@gmail.com', '2018-07-15'),
(49, 2, '200@gmail.com', '2018-07-16'),
(50, 1, '300@gmail.com', '2018-07-17'),
(51, 4, '300@gmail.com', '2018-07-18')
;

select * from `lesson`;

id | id_course | lesson_title
-: | --------: | :-----------
 1 |         3 | Урок 1  
 2 |         3 | Урок 2  
 3 |         3 | Урок 3  
 4 |         4 | Урок 4  
 5 |         4 | Урок 5

select * from `history`;

id | id_lesson | email         | date      
-: | --------: | :------------ | :---------
43 |         1 | 100@gmail.com | 2018-07-10
44 |         2 | 100@gmail.com | 2018-07-11
45 |         3 | 100@gmail.com | 2018-07-12
46 |         4 | 110@gmail.com | 2018-07-13
47 |         5 | 110@gmail.com | 2018-07-14
48 |         1 | 200@gmail.com | 2018-07-15
49 |         2 | 200@gmail.com | 2018-07-16
50 |         1 | 300@gmail.com | 2018-07-17
51 |         4 | 300@gmail.com | 2018-07-18

Получить курсы которые проходил ученик

select distinct 
  h.`email`,
  l.`id_course`
from `lesson` l
 join `history` h on l.`id` = h.`id_lesson`

email         | id_course
:------------ | --------:
100@gmail.com |         3
110@gmail.com |         4
200@gmail.com |         3
300@gmail.com |         3
300@gmail.com |         4

Получить полный список занятий, которые должен пройти ученик для завершения курсов (но не все занятия ученик прошел, например 200@gmail.com не прошел в курсе 3 занятие 3)

select 
  cr.`email`,
  cr.`id_course`,
  L0.`id` as id_lesson
from
  (
    select distinct 
      h.`email`,
      L.`id_course`
    from `lesson` L
      join `history` h on L.`id` = h.`id_lesson`
  ) as cr
  join `lesson` L0 on cr.`id_course` = L0.`id_course`
order by 
  cr.`email` asc,
  cr.`id_course` asc,
  L0.`id` asc

email         | id_course | id_lesson
:------------ | --------: | --------:
100@gmail.com |         3 |         1
100@gmail.com |         3 |         2
100@gmail.com |         3 |         3
110@gmail.com |         4 |         4
110@gmail.com |         4 |         5
200@gmail.com |         3 |         1
200@gmail.com |         3 |         2
200@gmail.com |         3 |         3
300@gmail.com |         3 |         1
300@gmail.com |         3 |         2
300@gmail.com |         3 |         3
300@gmail.com |         4 |         4
300@gmail.com |         4 |         5

Реальная таблица прохождения/непрохождения занятий (уроков)

-- полная таблица прохождения/непрохождения уроков
select 
  L1.`email`,
  L1.`id_course`,
  L1.`id_lesson`,
  H1.`email`
from
  (
  select 
    cr.`email`,
    cr.`id_course`,
    L0.`id` as id_lesson
  from
    (
      select distinct 
        h.`email`,
        L.`id_course`
      from `lesson` L
        join `history` h on L.`id` = h.`id_lesson`
    ) as cr
    join `lesson` L0 on cr.`id_course` = L0.`id_course`
  ) L1 
  left outer join `history` H1
  on 
    H1.`email` = L1.`email`
    and H1.`id_lesson` = L1.`id_lesson`
order BY 
  L1.`email`,
  L1.`id_course`,
  L1.`id_lesson`

email         | id_course | id_lesson | email        
:------------ | --------: | --------: | :------------
100@gmail.com |         3 |         1 | 100@gmail.com
100@gmail.com |         3 |         2 | 100@gmail.com
100@gmail.com |         3 |         3 | 100@gmail.com
110@gmail.com |         4 |         4 | 110@gmail.com
110@gmail.com |         4 |         5 | 110@gmail.com
200@gmail.com |         3 |         1 | 200@gmail.com
200@gmail.com |         3 |         2 | 200@gmail.com
200@gmail.com |         3 |         3 | null         
300@gmail.com |         3 |         1 | 300@gmail.com
300@gmail.com |         3 |         2 | null         
300@gmail.com |         3 |         3 | null         
300@gmail.com |         4 |         4 | 300@gmail.com
300@gmail.com |         4 |         5 | null

Выбираем кто не прошел курс

-- ученик не прошел весь курс
select distinct
  L1.`email`,
  L1.`id_course`
from
  (
  select 
    cr.`email`,
    cr.`id_course`,
    L0.`id` as id_lesson
  from
    (
      select distinct 
        h.`email`,
        L.`id_course`
      from `lesson` L
        join `history` h on L.`id` = h.`id_lesson`
    ) as cr
    join `lesson` L0 on cr.`id_course` = L0.`id_course`
  ) L1 
  left outer join `history` H1
  on 
    H1.`email` = L1.`email`
    and H1.`id_lesson` = L1.`id_lesson`
where 
  H1.`email` is null

email         | id_course
:------------ | --------:
300@gmail.com |         3
200@gmail.com |         3
300@gmail.com |         4

Получаем даты последних занятий курсов, которые прошел ученик

    select 
      h.`email`,
      L.`id_course`,
      max(h.`date`) as `max_date`
    from `lesson` L
      join `history` h on L.`id` = h.`id_lesson`
    group by 
      h.`email`,
      L.`id_course`

email         | id_course | max_date  
:------------ | --------: | :---------
100@gmail.com |         3 | 2018-07-12
110@gmail.com |         4 | 2018-07-14
200@gmail.com |         3 | 2018-07-16
300@gmail.com |         3 | 2018-07-17
300@gmail.com |         4 | 2018-07-18

РЕЗУЛЬТАТ: пользователи прошедшие курс

-- РЕЗУЛЬТАТ: пользователи прошедшие курс
select
  L3.email,
  L3.id_course,
  T07.max_date
from
  (
  select distinct 
    h.email,
    l.id_course
  from lesson l
    join history h on l.id = h.id_lesson
  ) L3 left outer join

  (
  select distinct
    L1.email,
    L1.id_course
  from
    (
    select 
      cr.email,
      cr.id_course,
      L0.id as id_lesson
    from
      (
        select distinct 
          h.email,
          L.id_course
        from lesson L
          join history h on L.id = h.id_lesson
      ) as cr
      join lesson L0 on cr.id_course = L0.id_course
    ) L1 
    left outer join history H1
    on 
      H1.email = L1.email
      and H1.id_lesson = L1.id_lesson
  where 
    H1.email is null
  ) L2 on 
      L2.email = L3.email
      and L2.id_course = L3.id_course
  join
  (
    select 
      h.email,
      L.id_course,
      max(h.date) as max_date
    from lesson L
      join history h on L.id = h.id_lesson
    group by 
      h.email,
      L.id_course
  ) as T07
  on
      T07.email = L3.email
      and T07.id_course = L3.id_course
where
 L2.email is null
 and L3.id_course = 4 -- Ограничиваем одним курсом 
;

email         | id_course | max_date  
:------------ | --------: | :---------
110@gmail.com |         4 | 2018-07-14

db<>fiddle here

Ничего себе! Спасибо вам большое. Только есть пару вопросов:

В последнем запросе нужно вывести ещё "date" последнего прошедшего урока, соответственно это и будет время прохождения всего курса.

Не понимаю как определяется id курса, т.к. в своей функции я его передавал как $id и использовал, а тут как?

И не могли бы вы написать пару источников с информацией о таких запросах, чтобы можно было их понять и разобрать самостоятельно, а то это слишком круто для меня пока что.

Заранее благодарю. — user8719712, Jul 10 '18 at 03:34
Использовался стандарт ansi sql, соединения join, только их больше двух, поэтому кажется все запутанным. Каждый шаг в https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=aaa3232996e12f32eaf5d841cf19c8de использует предыдущий запрос и приближает запрос к цели. Можно смотреть на результаты запросов, и проверять как происходит соединение (join) по данным. — 2SRTVF, Jul 10 '18 at 06:32
Спасибо вам, что потратили на меня время. Всё работает как надо. Буду разбираться что к чему. — user8719712, Jul 10 '18 at 06:51

Как правильно составить запрос для вывода тех, кто прошёл курс полностью?

2 Answers2