Relationship type id stored in the wrong custom group column?

Question

I made a custom group in a Relationship type. When I look back in MySQL table civicrm_custom_group I see that civicrm_relationship_type.id is stored in column civicrm_custom_group.extends_entity_column_value .

I am trying to write query that lists all custom groups in order to write an initialisation script. When doing a JOIN ON involving civicrm_custom_group.extends_entity_column_value, I discovered that is of type VARCHAR. I did not know that even with the MySQL CAST function it is impossible to use mixed types in a ON comparison.

Could it be bug that the id is stored in the wrong column? May be it should have been stored in column civicrm_custom_group.extends_entity_column_id ? That column is correctly of type UNSIGNED INT(10)

Answer 1

civicrm_custom_group.extends_entity_column_value stores more than just a number. You can have a custom group that extends a relationship type, but it can also be an activity type or a contact (sub-)type. If you extend a contact subtype, that field will contain the name of the subtype (not quite the same thing as the label btw), which will be in words.

The other thing is, that a custom group can extend more than one activity/relationship/contact subtype. How this is done, is by separating the values in extends_entity_column_value with the contents of CRM_Core_DAO::VALUE_SEPARATOR (ASCII SOH or 0x01), which isn't always visible when the data is shown to humans.

So, I have for extends_entity_column_value in one of my custom groups, <SOH>School<SOH>Further_Education_Institution<SOH>.

To unpack one of these kind of fields into an array, look at CRM_Utils_Array::explodePadded. (implodePadded being the reverse.) You could also match it using a regex- for example, this looks for a contact_sub_type field containing any of the subtypes School, Further Education Institution or Sixth Form College:

AND contact_a.contact_sub_type REGEXP CONCAT('(^|', x'01', ')School|Further_Education_Institution|Sixth_Form_College($|', x'01', ')')

Edit, for further info on where the IDs come from. extends_entity_column_value will contain SOH-separated:

• Numeric activity type IDs, if the custom group extends activities. Maybe null if it extends all activities. See also, civicrm_option_group's value column when option_group_id is 2 (you're looking at options in the activity_type option group).
• Numeric relationship type IDs, if the custom group extends relationships. Maybe null if it extends all relationships. See also, civicrm_relationship_type's id column.
• Contact subtype names, if the custom group extends a contact type. Maybe null if it extends all subtypes of a contact type. See also, civicrm_contact_type's name column. (If you look in civicrm_contact_type, and any of your contact (sub-)types have spaces/punctuation in their names you'll see the difference between the name and the label. The name is for use by code, the label is for humans to see.)

Custom field groups can also extend things other than contacts, relationships and activities. Events, participants and contributions for example.