Assignment of Activities just changed in CiviCRM

I came to upgrade a CiviCRM install to version 4.4.4 which contained various customisations and found that the civicrm_activity_assignment table had vanished! Along with civicrm_activity_target.

Here's what they've done, in case anyone else needs to update their custom reports like I did:

Both tables have been merged into a new civicrm_activity_contact table which has

  • contact_id
  • activity_id
  • record_type_id

The last one is an integer with the following meanings:

  1. Assignment record
  2. Added By
  3. With (aka "Target")

So in my SQL I had to:

  1. With/target stuff:
    1. replace civicrm_activity_target with civicrm_activity_contact
    2. replace target_contact_id with just contact_id
    3. AND add a filter for record_type_id = 3, so that we're finding targets and not assignments/adders.
  2. With assigmnent
    1. replace civicrm_activity_assignment with civicrm_activity_contact
    2. replace assignee_contact_id with contact_id
    3. AND add a filter for record_type_id = 1, so that we're finding targets and not assignments/adders.

I welcome this tidy-up of tables, it makes sense. I think I'd have been tempted to use an ENUM field because that would make a lot more sense when looking at the data, while maintaining the efficiency of an integer. I can hear the shouts of "the tables are not an API!", which is true, but you can't do everything with the API and most custom reports require knowledge of the table structures.

 

Tags: 
CiviCRM

Comments

enums are a mysql'ism, not a sql construct. doctrine does not support enums, so we are migrating away from enums :)

also the new table makes lots of queries 10-1000x more efficieint. a lot less left joins

lobo

Donald Lobo replied on

Understood. Thanks for sharing.

Enums are rather nice, though, where you're hard-coding integers that have simple meanings because you can have a policy of always referring to them as integers in your code, but your data makes sense when inspected at table level. But with this not being the normal way of accessing the data, and with the move to doctrine, I can see it's a sensible decision.

Rich replied on

I am also getting the same problem. I have just import all CiviCRM data from old version to new one but now getting issue with the Activity tables.

Sartaj replied on

Your data should be upgraded with the normal CiviCRM upgrade process? But yes, if like me you have code that relied on the table defs, you'll need to rewrite.

Rich replied on

Add new comment