Views 3 and Groups from CiviCRM

[NASACT]

I am trying to get Views to filter based on Groups but I have tried using the "Name" and the "ID" values but I get SQLSTATE[42S22]: Column not found: 1054 Unknown column 'civicrm_contact.group' in 'where clause' no matter what value I put there.  ANy thoughts?  Thanks!

[Erik Hommel]

A contact can be member of many groups, so there is no field civicrm_contact.group? You would have to look in the table civicrm_group_contact to see all groups for a contact.
I would recommend using the Webform - CiviCRM integration?

[NASACT]

Thanks, I will look into webform but I don't think that is going to help with filtering a Views criteria based on a specific group. Moreover I was looking on how to reference a group value in this instance as it forms the OpenLayers Data Layer for mapping members. Since I couldn't find a way to filter primary members only(mostly Orgs) I wanted to place those in a SmartGroup and then filter based on that group. Maybe I can't do either with the current state of Views integration?

[Coleman Watts]

I don't think views integration works with smart groups currently.

[petednz]

Hold that thought - i think we just cracked this - can't see it in jira though - possible I am getting confused but will report back

[torrance123]

I created a small module that provided views access to smartgroup contacts. It's not strongly tested so I'd welcome more eyes. Ideally, this is something that should be merged into core's Drupal module if it works fine (and this would remove the need for the use of hook_views_data_alter(), as these changes could be made directly).

The special thing with the civicrm_group_contact_cache table that this views code uses is that it needs to be rebuilt from time to time on a per smartgroup basis. I've included some logic that checks the cache status if a particular view is using a simple 'group id equals xxx' filter. A little more logic could handle other operators such as greater than, between, etc.

Without a filter on the group id, I don't think this would be a very useful bit of code as we'd either have a stale cache or we'd have to check the status and possibly rebuild the cache for every smartgroup, which could be prohibitively expensive.

See attached module (removed the '.txt' suffix — I had to add this to upload the file).

Code: [Select]

function civicrm_views_smartgroups_views_handlers() {
  return array(
    'handlers' => array(
      'civicrm_handler_filter_group' => array(
        'parent' => 'views_handler_filter_numeric',
      ),
    ),
  );
}

function civicrm_views_smartgroups_views_data() {
  $data = array();
  $data['civicrm_group_contact_cache']['table']['group'] = t('CiviCRM Smartgroup');
  $data['civicrm_group_contact_cache']['table']['join']['civicrm_contact'] = array(
    'left_field' => 'id',
    'field' => 'contact_id',
  );

  return $data;
}

function civicrm_views_smartgroups_views_data_alter(&$data) {
  $data['civicrm_group']['id']['filter']['handler'] = 'civicrm_handler_filter_group';

  $data['civicrm_group']['table']['join']['civicrm_contact'] = array(
    'left_table' => 'civicrm_group_contact_cache',
    'left_field' => 'group_id',
    'field' => 'id',
  );
}


Code: [Select]

class civicrm_handler_filter_group extends views_handler_filter_numeric {
  function pre_query() {
    civicrm_initialize();
    require $GLOBALS['civicrm_root'] . '/CRM/Contact/BAO/GroupContactCache.php';
   
    if ($this->operator == '=') {
      CRM_Contact_BAO_GroupContactCache::check($this->value['value']);
    }
  }
}


[NASACT]

Just so I understand the code here, we would use the explicit group id value (a #) in Views to filter not the display name for the group?

Also, I assume we are talking about integrating into the CiviCRM core for Drupal Views not Drupal Views module itself right?

[torrance123]

Just so I understand the code here, we would use the explicit group id value (a #) in Views to filter not the display name for the group?

That's right, it's doing only very rudimentary checks on the group id filter. If you were to filter on the display name for the group, you'd risk using an empty or stale cache for that smart group.

Also, I assume we are talking about integrating into the CiviCRM core for Drupal Views not Drupal Views module itself right?

Yes, that's right.

[Lionsharz]

Hi

This thread is old but I'm interested to know how this module is to be used exactly. Did you get it to work?

I have installed it on a Drupal 6 site but I see no additional info supplied by it to pull in the data from the SmartGroup.

Thanks for any advice you can give on this topic generally. If this module doesn't work, does anyone know of other work that's been done to pull in and display SmartGroup data in Drupal Views?

ta

[Donald Lobo]

pretty sure no work has been done to add this to core.

would be good if you can investigate and submit a PR against core for this so its included in future versions

lobo

[Lionsharz]

Hi Lobo,

The above code for Drupal 6 is limited in filtering abilities, but it does function.

I would like to pay for that code to be tweaked somewhat to allow and / or filtering on the group IDs and then ported to Drupal 7.

Further to that there was / is an issue with Drupal Groups being synced into Views which results in an SQL error. If that issue is still outstanding (from the chat on this forum it seems nobody is attending to Drupal integration code so I'm guessing it is still there as a bug) I will ask the developers to attend to this issue in the Drupal / Views integration code.

I have seen you mention Web Access as a go-to. Could you confirm that what I propose above is the right way to go about this? I'll contact you through web access for quotes on this work.

Thanks

[Donald Lobo]

You should chat with the folks at WA with regard to this. I do suspect these improvements are valuable to the larger community. Thanx for doing this

lobo

[Lionsharz]

Hi,

So I went ahead with the creation of a module that allows the data from a SmartGroup to pulled into Views in Drupal 7. Currently it functions but only on newly created SmartGroups, not existing ones.

One question came up with this development. Some SmartGroups seem to store the contacts in civicrm_group_contact, while other groups appear to be stored in civicrm_group_contact_cache. Those that are stored in the _cache table are flushed and removed (not avail to Drupal Views) while the others in civicrm_group_contact table are always available.

Anybody got any hints on why this is?

Also any advice on where developers can put this module for community? On drupal.org?

[Donald Lobo]

smart groups are query based and most of the dynamic contacts are stored in the _cache table. For the view, you can load them into the cache table (which ensures they stay there for a few minutes before being refreshed). The static members of the smart group are in the _contact table

wouldn't u just extend the current views integration rather than creating a new module? if so, might want to create a github account, fork the repo and then file a PR against the civicrm drupal repo

lobo

[Lionsharz]

Hi Lobo,

The issue we found with the current Views handler is that it searches the _cache table, which in many cases may be empty. In my case there are no permanent members (assigned manually) - all dynamic - so the views were always empty.

So the developers decided on creating an custom table to hold these Contacts. Views looks in there and pulls them from that position.

Is this a silly way to do things? Pointers?