Information about big Groups clean

[phanhan]

My team have created a bajillion groups, or thereabouts so it seems, and it seems that there's woefully little in the way of a naming convention (until now heh).

With this in mind, I'd like to do a big group cleanup, and help others do the same, by identifying old groups with low numbers of contacts. Through MySQL I looked for different group_type s but only got strange ASCII chars that look like squares with numbers in the middle. But even where group_type was null some smart groups were coming through, and my formula showed these as only having one member, when I know they have many more?

What am I missing?

SELECT civicrm_group.id, group_type, title, description, count(civicrm_group.id) AS count from civicrm_group LEFT JOIN civicrm_group_contact ON civicrm_group_contact.group_id = civicrm_group.id GROUP BY civicrm_group.id HAVING count = 1 ORDER BY count;

Thanks,

[Michael McAndrew]

My team have created a bajillion groups, or thereabouts so it seems, and it seems that there's woefully little in the way of a naming convention (until now heh).

It's a thankless task but somebody's got to do it! You are not alone

Through MySQL I looked for different group_type s but only got strange ASCII chars that look like squares with numbers in the middle.

Why did you want to do that?  Those ASCII chars that look like squares with numbers in the middle are the CiviCRM seperator.  To cut a long story short, if all you can see is that seperator, then not of the groups are classified into a group type.

even where group_type was null some smart groups were coming through

OK - group type is not meant to disntinguish between smart or not. If you look in the mysql table, you should see a column called saved_search_id which does that job.  Group type is more for Mailing list, ACL, or anything else that you want to define.

What am I missing?

Smart groups are calculated on the fly, hence your 1 member actually represents no members (it is probably a 1 because you are left joining).

To get the number of people in a smart group, you'll have to run the query (or potentially use the table civicrm_group_contact_cache, depending on your cache timeout settings, and the last time that smart group was looked up via the UI.