jethro-pmm icon indicating copy to clipboard operation
jethro-pmm copied to clipboard

Wrong query logic for service_component

Open tbar0970 opened this issue 3 years ago • 0 comments

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.

tbar0970 avatar Aug 06 '21 00:08 tbar0970