Author Topic: The big Groups cleanup (Read 457 times)

JohnFF · January 17, 2014, 03:56:33 am

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,

lolas · January 24, 2014, 06:14:16 am

The strange ascii characters are the separators used by civicrm when storing multiple values in one field. So the group_type column can indicate that the the group is a mailing list, an access control list, both or neither. If you try the query below you will see that the field length is 3 when one of those options is selected (even if you are using PHPMyAdmin which may not display the 0x01 separator characters).

Code: [Select]

select group_type, LENGTH(group_type), HEX(group_type) from civicrm_group
group by group_type;

I think this should be right for regular groups (adjust count limits to your needs):

Code: [Select]

SELECT 
g.id, group_type,g.name, g.description, count(g.id) AS group_count 
FROM civicrm_group g
LEFT JOIN civicrm_group_contact gc
ON gc.group_id = g.id 
WHERE saved_search_id IS NULL -- Regular groups
GROUP BY g.id 
HAVING group_count BETWEEN 1 AND 4
ORDER BY group_count ;

And this for Smart groups
(***NOTE** Use group_contact_cache instead but some contacts may have been added to the smart group manually so check both?)
You may want to run the scheduled job that updates the cache first:

Code: [Select]

SELECT 
g.id, group_type,g.name, g.description, count(g.id) AS group_count 
FROM civicrm_group g
LEFT JOIN civicrm_group_contact_cache gc -- ***NOTE** Use group_contact_cache instead
ON gc.group_id = g.id 
WHERE saved_search_id IS NOT NULL -- Smart groups
GROUP BY g.id 
HAVING group_count BETWEEN 1 AND 4
ORDER BY group_count ;

JohnFF · January 26, 2014, 04:46:22 am

Thanks Lolas, this has been hugely useful.

CiviCRM Community Forums (archive)

News:

Author Topic: The big Groups cleanup (Read 457 times)

JohnFF

The big Groups cleanup

lolas

Re: The big Groups cleanup

JohnFF

Re: The big Groups cleanup