1

The CiviCRM database stores multiple values as a varchar field (a string with separator characters). For example, the IDs 3, 9 and 15 are internally saved as #3#9#15# (where # is in fact the character Ctrl+A but displayed as # by phpMyAdmin).

This is strongly recommended against (see https://dba.stackexchange.com/questions/291361/transform-a-varchar-field-with-delimiters-into-multiple-rows ), but I suppose it will not be changed quickly.

For statistical reasons, I wanted to evaluate these varchar fields containing multiple IDs. Therefore, I needed to split them. But as storing data this way is not recommended, SQL has no built-in explode/split function for strings. Also, working with the built-in SQL function substring_index() is no choice because the number of selected options saved in this field varies, from 0 to 15. Additionally, the maximum number is not fix, as new options may be created from time to time.

So I want to share how I figured out to split these CiviCRM values with two SQL calls.

I know that I already posted part of this answer in the Database Administrators Stackexchange but I think this solution is highly CiviCRM specific and will probably be most useful to CiviCRM admins. So I wanted to post it here in a more detailed fashion.

2

For every option group (that is, set of multiple values) I created two SQL views: One for splitting the varchar field correctly, and one for combining the split values with other data. All of these "splitting views" in the database pull data from an additional, common view named match_ufid_contactid (matching the Drupal user ID uf_id with a CiviCRM contact) and another view named case_years (containing the information in which year the first consultation of this case took place, which is a custom field in this system).

So, let's say we have a custom field/column case_result of type multiple-choice (residing in the normal CiviCRM database called civi_db here, in a table called result_table here). We are splitting this column into single values with the following SQL view named stringsplit_case_result (residing in a second statistics database called stats_db here):

select `zz`.`case_id` AS `case_id`,
       `zz`.`contact_id` AS `contact_id`,
       `zz`.`one_split` AS `one_split`
from
     (select cast(coalesce(nullif(substring_index(substring_index(`content_alias`.`fieldset_column`,char(1),`num`.`id`),char(1),-1),''),'0')
         as signed) AS `one_split`,
         `content_alias`.`case_id` AS `case_id`,
         `content_alias`.`contact_id` AS `contact_id`
     from
          ((select `fieldset_table`.`case_result` AS `fieldset_column`,
                   `case_contact`.`case_id` AS `case_id`,
                   `case_contact`.`contact_id` AS `contact_id`
          from (`civi_db`.`result_table` `fieldset_table`
          join `civi_db`.`civicrm_case_contact` `case_contact`
              on(`case_contact`.`case_id` = `fieldset_table`.`entity_id`))) `content_alias`
          join
              (select `a`.`h` * 10 + `b`.`t` + 1 AS `id`
              from ((select 0 AS `h` union select 1 AS `1` union select 2 AS `2` union select 3 AS `3` union select 4 AS `4` union select 5 AS `5` union select 6 AS `6` union select 7 AS `7` union select 8 AS `8` union select 9 AS `9`) `a`
              join (select 0 AS `t` union select 1 AS `1` union select 2 AS `2` union select 3 AS `3` union select 4 AS `4` union select 5 AS `5` union select 6 AS `6` union select 7 AS `7` union select 8 AS `8` union select 9 AS `9`) `b`)) `num`)
     where `num`.`id` > 0) `zz`
where `zz`.`one_split` > 0
order by `zz`.`one_split`

Then, inside stats_db, we create another SQL view that takes the split values from the stringsplit_case_result view and matches them with useful information we need for statistics (namely, the CaseID, the ContactID, the Drupal user ID, the year of first consultation, the human-readable label of this split value):

select `stringsplit`.`case_id` AS `case_id`,
       `stringsplit`.`contact_id` AS `contact_id`,
       `stringsplit`.`one_split` AS `one_split`,
       `option_table`.`label` AS `label`,
       `uf_table`.`uf_id` AS `uf_id`,
       years_table.first_contact AS first_contact
from `stats_db`.`stringsplit_case_result` `stringsplit`
    join (select `optionvalue`.`label` AS `label`,
                 `optionvalue`.`value` AS `value`
         from `civi_db`.`civicrm_option_value` `optionvalue`
        where `optionvalue`.`option_group_id` = 136) `option_table`
    join `stats_db`.`match_ufid_contactid` `uf_table`
    join `civi_db`.civicrm_case `case_table`
join `stats_db`.case_years `years_table`
where case_table.is_deleted = 0
  and `stringsplit`.`one_split` = `option_table`.`value`
  and `uf_table`.`contact_id` = `stringsplit`.`contact_id`
  and `stringsplit`.`case_id` = case_table.id
and stringsplit.case_id = years_table.case_id

These views rely on two additional SQL views called case_years and match_ufid_contactid (both inside stats_db). case_years supposes there is a table called first_consultation with a column date in the civi_db database, where date is in SQL date format. You can find them here:

match_ufid_contactid:

select `civi_db`.`civicrm_uf_match`.`uf_id` AS `uf_id`,
`civi_db`.`civicrm_uf_match`.`contact_id` AS `contact_id` 
from `civi_db`.`civicrm_uf_match`

and case_years:

select `case_table`.`id` AS `case_id`,
extract(year from `first_consultation`.`date`) AS `first_contact` 
from 
(`civi_db`.`civicrm_case` `case_table` 
join `civi_db`.`first_consultation` `first_consultation`) 
where `case_table`.`id` = `first_consultation`.`entity_id`

Edit: @petednz In our system, Drupal Views doesn't recognize the CiviCRM fields as multiple value fields, which is why it doesn't allow to split the fields in Drupal.

3
  • Thanks for the write up and some heavy-duty SQL! Another option if it's for reporting is to set up the custom fields as two fields each: primary and secondaries, where primary is single-valued, and only primary gets used for reporting, and the secondaries can if need be just included as an "extra info" column without any processing except maybe comma separators instead of char(1). This way you can avoid both the technical issues and possible misinterpretation of multivalued data (e.g. double-counting).
    – Demerit
    Sep 28 at 13:27
  • agree it is great to have the write up. thank you
    – petednz - fuzion
    Sep 28 at 19:23
  • fwiw, i have a vague, and possibly incorrect memory, of being able to split the values in a Drupal View, a long time ago.
    – petednz - fuzion
    Sep 28 at 19:24
1

While not a SQL-specific answer, I would typically use an ETL tool like Pentaho Data Integration to do the data transforms SQL isn't well-suited for. PDI has both a "Split fields" and "Split fields into rows" steps - depending on whether you want your data normalized or denormalized.

To deal with the control character issue, I'll generally use UNHEX(01) though it seems like your CHAR(1) serves the same purpose.

1
  • Finally - you're right that this isn't going to change soon. However, with this summer's release of MariaDB 10.6 supporting the JSON_TABLE() function that MySQL 8.0 does, and with CiviCRM APIv4 work standardizing the metadata (sadly, there's something like 4 ways to store serialized data in a table Civi uses), we're moving toward a future where it will be simple to move all serialized data to JSON structures. Sep 28 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.