is it possible to create a smart group based on CMS user information from Drupal

[daveatkins]

Is there a way to expose additional database information from Drupal users to the search interface so it can be used to form smart groups? An example of what I would like to do is create a smart group to find all contacts whose connected Drupal accounts belong to certain organic groups and have certain roles in those groups.

for example, first, I find the information for a given Drupal user with uid=3085 with a query like this:

Code: [Select]

select u.name, ur.gid, n.title
from og_users_roles ur
join users u on ur.uid=u.uid
join node n on ur.gid=n.nid
join content_type_xxxx ctcc on ctcc.nid=ur.gid and ctcc.field_inheritance_value<>1
where u.uid=3085 and ur.rid=3;
this gives me a list of the Drupal organic groups to which the user belongs and has role=3 (and some other characteristic about the group in the custom field value "inheritance_value".)

in Civi, advanced search, I would like to be able to choose a "gid" from a dropdown list based on something like this:

Code: [Select]

select n.title, og.nid
from og
join node n on og.nid=n.nid
join content_type_xxxx ctcc on ctcc.nid=og.nid and ctcc.field_inheritance_value<>1;
Assuming a valid gid is 8190, then the civi query that finds all contacts who should be in the smart group would be:

Code: [Select]

select cc.display_name
from civicrm_contact cc
join civicrm_uf_match cuf on cuf.contact_id=cc.id
join users u on cuf.uf_id=u.uid
join og_users_roles ur on ur.uid=u.uid and ur.rid=3
where gid=8190;
Right now, we implement this functionality by running scripts that actually create the groups. But this is a pain to keep in sync and enforce the desired one-way relationship (I cannot allow Civi users to manage these groups because they would bypass the scripts and add users who are not in the Drupal groups).

[petednz]

Dave - what sort of Roles are we talking about - OG Members v OG Admin?

IF yes have you tried the OG Synch approach which would basically give you those two types of Smart Groups in civi for each OG?

If not, I will watch with interest

[daveatkins]

I am talking about OG roles
we are using the OG sync approach and it is very problematic.
We have hundreds of OGs--very hierarchical structure. We do not want ALL those groups to show up in Civi, only certain ones as defined by my query above (that is about 68 groups right now). We want a "one-way" relationship from Drupal to Civi because other actions in Drupal impact the OG status...for example, when a user is added or removed from a Drupal role, this causes them to automatically be added or removed from certain OG groups (depending on additional characteristics of the groups). Sorry, it is hard to describe in the abstract, but suffice it to say, there is no way a complete integration will work without a lot more effort and changing the way the site was implemented, so my immediate challenge is to figure out if there is a way to avoid the current bad idea of maintaining separate groups in civi and drupal OG and trying to keep them in sync.

[Hershel]

Is there a way to expose additional database information from Drupal users to the search interface so it can be used to form smart groups?

Someone else will respond, but I think this will be somewhat difficult.

Right now, we implement this functionality by running scripts that actually create the groups. But this is a pain to keep in sync and enforce the desired one-way relationship (I cannot allow Civi users to manage these groups because they would bypass the scripts and add users who are not in the Drupal groups).

Why is this a pain? If your list of OG Roles is fixed, why can't the script delete the groups it made and then recreate them every day? It should be something you can automate, no?

[Donald Lobo]

The easiest option might be to:

1. Write a custom search to expose the additional DB information. You can add whatever fields you want to the custom search (since you are also supplying the query, civi does not need to know anything about those fields)

2. Use the above custom search to create a smart group

3. Use the smart group in your future searches

lobo

[daveatkins]

thanks for the custom search idea

Hershel--one problem we had with regenerating the groups is that this breaks any user-defined smart groups because when the groups are recreated in civi, they get new group ids

in principle, the re-build daily approach *should* work, but most of the time the workflow does not allow for such a delay. Example: I am planning to send a communication to my committee, but we have a couple of new people. I add them to the drupal organic group and immediately go to send out the email. I can't wait for an overnight job to run. So we could trigger an update of the civi group upon every change to the organic group...but still not ideal due to the smart group problem with changing civi group ids.