CiviCRM Community Forums (archive)

Hey, this is unsettling. Have 2585 members in a group. I need to remove some so am using the method of adding all to a new group, then finding my subset to keep, and removing from the new group, so my new group ends up with those i need to delete (cause I can't specify 'in this group but not in that group')

So Adv Search shows me 2585 members. I go 'Add to Group' having selected All, and it says "Number of selected contacts: 2655"

Pictures attached for those who like them.

ok chris is coming back with the log. in doing this i found some other issues.
1/ there are 2655 in the group, not 2585
2/ Adv search tells me there are only 2585 and stops on the page on which # 2585 would be, but actually shows records 2586-2600.
3/ record 2600 is for wilson, alison
3/ if i reverse sort z-a i can confirm that there are an additional 55 names now showing on page 1 (zwart to wilson, anne).

So my non-sql log analysis is that the AdvSearch query is not counting 70 records - and endeavours not to display them too!

Hi Lobo

These are the two queries which disagree:

This one - with 2585 results:
    SELECT count(DISTINCT contact_a.id)
      FROM civicrm_contact contact_a
 LEFT JOIN civicrm_group_contact `civicrm_group_contact-100`
        ON contact_a.id = `civicrm_group_contact-100`.contact_id
     WHERE  ( `civicrm_group_contact-100`.group_id IN (100)
       AND `civicrm_group_contact-100`.status IN ("Added") )
       AND ( 1 )  ;


And this one - 2655 results:
    SELECT DISTINCT(contact_a.id) as contact_id, contact_a.contact_type as contact_type, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name as sort_name, contact_a.display_name as display_name, contact_a.do_not_email  as `do_not_email`, contact_a.do_not_phone  as `do_not_phone`, contact_a.do_not_mail  as `do_not_mail`, contact_a.do_not_trade  as `do_not_trade`, contact_a.is_opt_out  as `is_opt_out`, contact_a.legal_identifier  as `legal_identifier`, contact_a.external_identifier  as `external_identifier`, contact_a.nick_name  as `nick_name`, contact_a.legal_name  as `legal_name`, contact_a.home_URL  as `home_URL`, contact_a.image_URL  as `image_URL`, contact_a.preferred_mail_format  as `preferred_mail_format`, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, contact_a.job_title  as `job_title`, contact_a.birth_date  as `birth_date`, contact_a.is_deceased  as `is_deceased`, contact_a.deceased_date  as `deceased_date`, contact_a.household_name  as `household_name`, contact_a.organization_name  as `organization_name`, contact_a.sic_code  as `sic_code`, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_state_province.id as state_province_id, civicrm_state_province.abbreviation as `state_province`, civicrm_state_province.name as state_province_name, civicrm_country.id as country_id, civicrm_country.name as `country`, civicrm_phone.id as phone_id, civicrm_phone.phone_type as phone_type, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_im.id as im_id, civicrm_im.name as `im`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`, gender.value as gender_id, gender.label as gender, `civicrm_group_contact-100`.id as group_contact_id, `civicrm_group_contact-100`.status as status 
      FROM civicrm_contact contact_a
 LEFT JOIN civicrm_address
        ON ( contact_a.id = civicrm_address.contact_id
       AND civicrm_address.is_primary = 1 )
 LEFT JOIN civicrm_state_province
        ON civicrm_address.state_province_id = civicrm_state_province.id
 LEFT JOIN civicrm_country
        ON civicrm_address.country_id = civicrm_country.id
 LEFT JOIN civicrm_email
        ON (contact_a.id = civicrm_email.contact_id
       AND civicrm_email.is_primary = 1)
 LEFT JOIN civicrm_phone
        ON (contact_a.id = civicrm_phone.contact_id
       AND civicrm_phone.is_primary = 1)
 LEFT JOIN civicrm_im
        ON (contact_a.id = civicrm_im.contact_id
       AND civicrm_im.is_primary = 1)
 LEFT JOIN civicrm_worldregion
        ON civicrm_country.region_id = civicrm_worldregion.id 
 LEFT JOIN civicrm_group_contact `civicrm_group_contact-100`
        ON contact_a.id = `civicrm_group_contact-100`.contact_id 
 LEFT JOIN civicrm_option_group option_group_gender
        ON (option_group_gender.name = 'gender' AND option_group_gender.domain_id = 1 )
 LEFT JOIN civicrm_option_value gender
        ON (contact_a.gender_id = gender.value
       AND option_group_gender.id = gender.option_group_id)
     WHERE ( `civicrm_group_contact-100`.group_id IN (100)
       AND `civicrm_group_contact-100`.status IN ("Added") )
       AND ( 1 ) ORDER BY contact_a.sort_name asc

I think what's happening is that the first query gets the real number of contacts, and the second query includes some contacts repeated because they have multiple details (eg email addresses). Removing all but the first selected column makes the numbers match. (And, LEFT JOINs shouldn't add any to the resultset anyway.) Therefore I think the first query has the correct result count.

(Pete - summarising the above - I think 2585 is the correct result, and that it's correct in not showing them.)

We have some entries with multiple is_primary. I can find 14, 11 and 12 results with these three queries; they all have two is_primary for whatever detail is represented.

select civicrm_contact.id, count(civicrm_email.id) as e from civicrm_contact, civicrm_email where civicrm_email.contact_id = civicrm_contact.id and is_primary = 1 group by contact_id  having e > 1 ;
select civicrm_contact.id, count(civicrm_address.id) as e from civicrm_contact, civicrm_address where civicrm_address.contact_id = civicrm_contact.id and is_primary = 1 group by contact_id  having e > 1  ;
select civicrm_contact.id, count(civicrm_phone.id) as e from civicrm_contact, civicrm_phone where civicrm_phone.contact_id = civicrm_contact.id and is_primary = 1 group by contact_id  having e > 1 ;