CiviCRM Community Forums (archive)

I have a couple questions about groups. I have a smart group setup that contains only organizations (criteria is specific to the org contact type). However, I need to run a list of individuals employed by orgs in this group. The CiviCRM interface doesn't provide for a way to bridge that org-ind gap, so I was just going to build a query outside of CiviCRM to export my data.

But I was looking at the schema and there are two mystery tables associated with groups -- civicrm_group_nesting and civicrm_group_organization. The _nesting table would appear to be intended for having hierarchal groups (group within a group, or perhaps to simply organize groups better). The _organization table appears to anticipate what I need -- view contacts associated with orgs that are included in a group.

Am I missing an existing feature that can accomplish what I need, or were these tables built anticipating future functionality (I recall 2.1 has some group reworking on the roadmap)?

-Brian

I don't think there's an existing feature, but I was just looking at the query in the example for custom searches here:

http://wiki.civicrm.org/confluence/display/CRMDOC/Custom+Search+Components

This custom search returns individuals and current employers. I was thinking it might work for you if you just modify the sql and the search parameters a little. First have the search param be on group rather than last name/state. Then you want to join the civicrm_group_contact table (this holds your org's id and the group id). Then you want to have the WHERE statement switch out the current parameters to take in the group that's checked off in your custom search form. Let me preface by saying that I am not great at mysql, but the two additions you need would be something like this:

LEFT JOIN civicrm_group_contact gc ON (cR.contact_id_b = gc.contact_id)

WHERE gc.group_id IN ('$your_group_id_here')

$your_group_id_here would be pulled from the checkbox on the search form.

If you do it this way, you get the benefit of your search staying within the CiviCRM interface, and the form is very close to complete if you follow the tutorial and just replace the search params and adjust the mysql statement slightly.

If you still want to build your own query outside CiviCRM, you should only need the civicrm_group_contact table. I don't think group_nesting table is necessary (unless of course your group is a nested group). So far as I can see civicrm_group_organization isn't important either, and I don't think it's being used.

hope this is somewhat useful.