CiviCRM Community Forums (archive)

This is a bug, which I have solved (after a lot of debuging... ), and which I would file an issue for, but I thought it easier to post a note here and let more experienced hands fix it up in the code base.

I discovered the problem while trying to expose custom data in the Drupal views module, but since the bug is in CRM/Core/BAO/CustomGroup.php, I'll post it here.

For some background, see this thread:
http://forum.civicrm.org/index.php/topic,13152.30.html

Basically, I noticed that Custom Data groups marked as "Tab" style instead of "Inline" style did not appear in views. In the Views2 Integration module, the function CRM_Core_BAO_CustomGroup::getTree is called (around line 5067 in civicrm.views.inc).

The db query that getTree was calling is not properly formatted in the where statement (NULLAND):

...
WHERE civicrm_custom_group.is_active = 1 AND civicrm_custom_field.is_active = 1
AND civicrm_custom_group.extends IN ('Individual', 'Contact')
AND civicrm_custom_group.extends_entity_column_value IS NULLAND civicrm_custom_group.id = 4    <--- Whoops!
AND civicrm_custom_group.id IN ( 1,2,4,7,5 )
ORDER BY civicrm_custom_group.weight, civicrm_custom_group.title,
civicrm_custom_field.weight, civicrm_custom_field.label

Reviewing the getTree function, I see around line 358 in CustomGroup.php (near the infamous 328: http://forum.civicrm.org/index.php?topic=11654.0 )

        if ( $subType ) {
            $subType  = CRM_Core_DAO::VALUE_SEPARATOR .
                trim($subType, CRM_Core_DAO::VALUE_SEPARATOR) . CRM_Core_DAO::VALUE_SEPARATOR;
            $strWhere = "
WHERE civicrm_custom_group.is_active = 1
  AND civicrm_custom_field.is_active = 1
  AND civicrm_custom_group.extends IN ($in)
  AND ( civicrm_custom_group.extends_entity_column_value LIKE '%$subType%'
   OR   civicrm_custom_group.extends_entity_column_value IS NULL )        <--- NO space
";
            if ( $subName ) {
                $strWhere .= " AND civicrm_custom_group.extends_entity_column_id = {$subName} ";
            }
        } else {
            $strWhere = "
WHERE civicrm_custom_group.is_active = 1
  AND civicrm_custom_field.is_active = 1
  AND civicrm_custom_group.extends IN ($in)
  AND civicrm_custom_group.extends_entity_column_value IS NULL            <--- NO space
";
        }
 

The MySQL fragments end with newlines, and have no space. Apparently, the newlines must be stripped before being handed to the database, and are not replaced with spaces. The same error appears further up in the same block, but because of the close bracket it must not cause MySQL to fall over.

After cleaning this up, I notice that the database queries for the Views integration are cleaner (returning the intended result, rather than all the results...)

Would be handy for this to get fixed so I don't have to keep manually fixing it every time I upgrade.

Keep up the good work, guys!

am a bit surprised that a newline and space are not interchangeable. do you know which code (is it in the views2 code base?) removes newlines?

lobo

I've also been having difficulty getting a multiple record custom data set to appear in views' fields.  @uayebforever's fix worked for me, though I'm not completely sure why yet, nor if it's going to break something...

Starting at line 5059 in civicrm.views.inc:
function civicrm_views_custom_data_cache($data, $entity_type, $groupID, $subType, $style) {

    //Feels a bit hacky this next 10 lines but it was the only way I could get getTree to play nice with the data
    $config =& CRM_Core_Config::singleton( );
    /*
    if ($style == 'Inline') {
        $tree = CRM_Core_BAO_CustomGroup::getTree( $entity_type, CRM_Core_DAO::$_nullObject, null, null, $subType, null );
    }
    else {
        $tree = CRM_Core_BAO_CustomGroup::getTree( $entity_type, CRM_Core_DAO::$_nullObject, null, $groupID);
    }
    */
    //from http://forum.civicrm.org/index.php/topic,17658.msg73901.html#msg73901
    $tree = CRM_Core_BAO_CustomGroup::getTree( $entity_type, CRM_Core_DAO::$_nullObject, null, $groupID, $subType, null );