У меня есть 3 таблицы с именем
которая имеет отношение между первыми двумя таблицами.
Ниже приведены поля таблиц
com_event_schedules – id – name – schedule_date – start_time – end_time – удален
com_appointments – id – start_time – end_time – статус
com_event_schedules_com_appointment_c – id – com_event_schedules_com_appointmentcom_event_schedules_ida (schedule_id) – com_event_schedules_com_appointmentcom_appointment_idb (destination_id)
отношение между таблицами com_event_schedule и com_appointments равно 1 для многих
То, что я хочу получить с помощью sched_id, и общее количество его назначений при условии status = 'completed'
Я попробовал следующий запрос:
SELECT sch.id,COUNT(app.id) AS total, (SELECT COUNT(ap.id) FROM com_appointment ap, com_event_schedules sc, com_event_schedules_com_appointment_c re WHERE re.com_event_schedules_com_appointmentcom_event_schedules_ida=sc.id AND ap.id=re.com_event_schedules_com_appointmentcom_appointment_idb AND sc.deleted=0 AND ap.status='completed') AS completed FROM com_event_schedules sch, com_appointment app, com_event_schedules_com_appointment_c rel WHERE rel.com_event_schedules_com_appointmentcom_event_schedules_ida=sch.id AND app.id=rel.com_event_schedules_com_appointmentcom_appointment_idb AND sch.deleted=0 GROUP BY sch.id
Используя этот запрос, я получаю точный общий счет, но завершенный счет не так, как ожидалось. он показывает 1 для каждого расписания. Однако только 1 назначение в db завершено, а другие все еще находятся на рассмотрении.
Что-то не так с запросом? У меня есть SugarCRM в backend. Не могу использовать скрипт, потому что поля и поля слишком грязные.
Этот запрос должен помочь вам. Самое большое, что он делает, это подсчитать ВСЕ назначения для общего числа, а затем SUM по статусу IF = завершено, чтобы получить как общее, так и завершенное в том же запросе.
SELECT sc.id, COUNT(ap.id) as total, SUM(IF(status = 'completed', 1, 0)) as completed FROM com_event_schedules sc LEFT JOIN com_event_schedules_com_appointment_c re ON re.com_event_schedules_com_appointmentcom_event_schedules_ida = sc.id LEFT JOIN com_appointment ap ON re.com_event_schedules_com_appointmentcom_appointment_idb = ap.id WHERE sc.deleted = 0 GROUP BY sc.id
Кроме того, я заметил, что вы сказали, что это отношения от одного до многих. Реляционные таблицы, как и у вас, действительно для многих для многих. Самый эффективный способ иметь от одного до многих – избавиться от таблицы com_event_schedules_com_appointment_c
и добавить com_event_schedule_id
в таблицу com_appointments
.