CiviCRM Community Forums (archive)

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:

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:

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:

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

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.

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