MySQL Event and Activities on Upgrade

[JohnFF]

Hi gang,

I'm in the process of upgrading my humble 4.2 install to a rocking 4.4.4.

We have quite a lot of custom modules, including a client interface that produces a page showing relevant activities and events. Of course, as the activities structure has changed, we need to update it to reflect the new change.

It's a little bit complicated, and I've got a little bit stuck. If anyone could help me, even just giving me pointers, I would be most grateful!

The working 4.2 version:

SELECT ca.id as mobID, cov.label as mobilisation, IF(ce.id IS NULL, ca.activity_date_time, ce.start_date) as date, IF(ce.id IS NULL, ca.activity_date_time, ce.end_date) as end_date, ca.details, ce.id as event_id, ce.title, eventc.*, actc.*
FROM civicrm_activity ca INNER JOIN civicrm_option_value cov ON ca.activity_type_id = cov.value
INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id AND cog.name = 'activity_type'
LEFT JOIN civicrm_event ce ON ca.source_record_id = ce.id AND ce.is_active = 1
LEFT JOIN civicrm_value_school_events_data_19 eventc ON ce.id = eventc.entity_id
LEFT JOIN civicrm_value_mobilisation_activity_data_397 actc ON ca.id = actc.entity_id
WHERE cov.label IN ('Careers', 'Non Careers', 'Work Experience', 'Mentor', 'Donation', 'Governor', 'Other') AND ca.source_contact_id = %1
ORDER BY ca.activity_date_time DESC

And my not working 4.4 version

SELECT ca.id as mobID, cov.label as mobilisation, IF(ce.id IS NULL, ca.activity_date_time, ce.start_date) as date, IF(ce.id IS NULL, ca.activity_date_time, ce.end_date) as end_date, ca.details, ce.id as event_id, ce.title, eventc.*, actc.*
FROM civicrm_activity ca INNER JOIN civicrm_option_value cov ON ca.activity_type_id = cov.value
INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id AND cog.name = 'activity_type'
INNER JOIN civicrm_activity_contact AS cac ON cac.activity_id = ca.id
LEFT JOIN civicrm_event ce ON ca.source_record_id = ce.id AND ce.is_active = 1
LEFT JOIN civicrm_value_school_events_data_19 eventc ON ce.id = eventc.entity_id
LEFT JOIN civicrm_value_mobilisation_activity_data_397 actc ON ca.id = actc.entity_id
WHERE cov.label IN ('Careers', 'Non Careers', 'Work Experience', 'Mentor', 'Donation', 'Governor', 'Other') AND (cac.contact_id = %1 AND record_type_id = 1)
ORDER BY ca.activity_date_time DESC

Thanks a lot in advance,

John

[JohnFF]

It turns out that I was using the wrong activity contact type index.

1 Assigned
2 Source
3 Target

+1 artfulrobot.com!