Custom Groups bug with Drupal Views

[uayebforever]

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):

Code: [Select] [nofollow]

...
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 )

Code: [Select] [nofollow]

        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!

[uayebforever]

Hrm. Seems I didn't fix the whole thing: There was a bit of a hack in civicrm/drupal/modules/views/civicrm.views.inc which must have been motivated by the aforementioned bug.

Starting at line 5059:

Code: [Select] [nofollow]

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);
    }

I believe should now just be:

Code: [Select] [nofollow]

function civicrm_views_custom_data_cache($data, $entity_type, $groupID, $subType, $style) {

    $config =& CRM_Core_Config::singleton( );
    $tree = CRM_Core_BAO_CustomGroup::getTree( $entity_type, CRM_Core_DAO::$_nullObject, null, $groupID, $subType, null );

Thus $tree then just contains the necessary information for the particular group of custom fields in this iteration of the loop calling civicrm_views_custom_data_cache earlier in the file.

Now I think I can go to bed.

[Donald Lobo]

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

[uayebforever]

Hrm. I would have thought so as well, but am not sure how the composting of the strings is done before it is passed to the SQL server. It appears this happens inside $crmDAO =& CRM_Core_DAO::executeQuery( $queryString, $params );, but I haven't looked a the details of executeQuery

Looking at this again this morning, I notice that the next string to be concatenated starts with a space:

Code: [Select] [nofollow]

            $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
";
        }
 
        $params = array( );
        if ( $groupID > 0 ) {
            // since we want a specific group id we add it to the where clause
            $strWhere .= " AND civicrm_custom_group.id = %1";           <--- Begins with a space
            $params[1] = array( $groupID, 'Integer' );
        } else if ( ! $groupID ){
            // since groupID is false we need to show all Inline groups
            $strWhere .= " AND civicrm_custom_group.style = 'Inline'";   <--- Begins with a space
        }

So maybe I'm barking up the wrong tree after all of this. But then something must have generated the query above, and I think it was before I started editing CustomGroup.php.

If I get a chance, I'll revert to the stock 3.3.1 code for these two files, and see if I can't reproduce it again.

Andy

[fen]

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:

Code: [Select]

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 );