jethro-pmm
jethro-pmm copied to clipboard
Wrong query logic for service_component
From service::saveItems() --> serviceComponent::getDBObjectData() we get
SELECT service_component.id, service_component.title, service_component.alt_title, service_component.categoryid, service_component.comments, service_component.ccli_number, service_component.runsheet_title_format, service_component.personnel, service_component.length_mins, service_component.show_in_handout, service_component.handout_title_format, service_component.show_on_slide, service_component.credits, GROUP_CONCAT(DISTINCT cong.name SEPARATOR ", ") as congregations, IF (LENGTH(service_component.runsheet_title_format) = 0, cat.runsheet_title_format, service_component.runsheet_title_format) as runsheet_title_format , IF (LENGTH(service_component.personnel) = 0, cat.personnel_default, service_component.personnel) as personnel , COUNT(DISTINCT svc12m.id) AS usage_12m, MAX(svc.date) as lastused
FROM service_component
JOIN service_component_category cat ON cat.id = service_component.categoryid
LEFT JOIN congregation_service_component csc ON csc.componentid = service_component.id
LEFT JOIN congregation cong ON cong.id = csc.congregationid
LEFT JOIN service_item si ON si.componentid = service_component.id
LEFT JOIN service svc ON svc.id = si.serviceid AND svc.congregationid = cong.id
LEFT JOIN service svc12m ON svc12m.id = svc.id AND svc12m.date > NOW() - INTERVAL 12 MONTH
WHERE ((service_component.id IN (199, 573, 208, 560, 269, 182, 517, 351, 433, 572, 250, 142, 207, 203, 571))) OR cong.id IS NOT NULL
GROUP BY service_component.id
Note "OR cong.id IS NOT NULL" - this shouldn't be happening. It should at least be AND cong.id IS NOT NULL. Related to the slow query bug.