Seeing Different Record Sets between Find Contacts and Profile which searches

[mdlueck]

Greetings,

We are trying to make sure only the proper data is showing up in our search-able Profile / "Rolodex" of organizations and not any constituents. I developed the following query to aid the task... as some individuals ended up getting tagged into the search-able DB "Rolodex" and thus were still showing up.

Code: [Select]

SELECT `civicrm_contact`.`id`, `civicrm_group_contact-6`.`status`, `civicrm_contact`.`contact_type`, `civicrm_contact`.`sort_name`, `civicrm_contact`.`display_name`, `civicrm_contact`.`organization_name`
FROM `civicrm_contact`, `civicrm_group_contact` AS `civicrm_group_contact-6`
WHERE `civicrm_contact`.`id` = `civicrm_group_contact-6`.`id`
AND `civicrm_group_contact-6`.`group_id` = 6
ORDER BY `civicrm_contact`.`id`;
There are yet some disturbing points. Shortly, if we go through Find Contacts, specify "in" the "Rolodex" group we only see contacts which should be existing, aka "Added" status. This seems to be working as expected.

If we use the "Rolodex" search profile, we are seeing ghost records that are not really any longer in the "Rolodex". Via the Profile which searches, it also states that records need to be in the "Added" status.

Further, this query above still shows records "status = Added" which we can go to the particular `civicrm_contact`.`id` and the record does not show that it is currently in the "Rolodex".

The Find Contacts path seems to be returning the correct record set... how do we get our Search Profile to return the same record set as Find Contacts. (And as a bonus, how can I rework my query to return the same?)

Aahh, one more difference between Find Contacts and the Search Profile / my query: Certain contacts we have either taken out of the "Rolodex" group status, or deleted all together. Those records are STILL showing up in the Search Profile, and in my query as "status = Added" still. Those records are GONE from Find Contacts. Either way (removed the group status / deleted the record), such records should not show up in the Search Profile.

Thank you!!

[mdlueck]

I enabled MySQL logging, did a Search \ Find Contacts, specified "in" our "Rolodex" group, and cycled through all of the entries.

Stopped Apache / MySQL, and renamed the log to include "FindContacts" in the filename.

I started MySQL / Apache, and immedialtely ran our Profile Search. Again scrolled through the entire contact list.

Stopped Apache / MySQL, and renamed the log to include "ProfileSearch" in the filename.

Disabled logging and put the website back into production mode.

Diffed the outputs... expecting to see one query selecting records only in civicrm_group_contact.status = 'Added' and the other one not. That part of the query was indeed very similar.

However slightly before that, the FindContacts (the one that works correctly) has an INNER JOIN added:

Code: [Select]

INNER JOIN civicrm_contact_type parent ON subtype.parent_id = parent.id
WHERE  subtype.name IS NOT NULL AND subtype.parent_id IS NOT NULL 
 AND subtype.is_active = 1 AND parent.is_active = 1 ORDER BY parent.id

Perhaps could that be the reason that the Profile Search is picking up ghost data?

I am trying to attach the log file portions which I diffed... aaakkk, allowed types... I need to add a .txt to the filenames... NOW let's try and see if the files make it in correctly.

[mdlueck]

Bump

Could someone please look into the SQL traces I ran associated with this problem which causes "ghost records" to show up in Profiles with search capabilities?

[lcdweb]

I can confirm on 3.3.3+ that deleted contacts (trashed) are included in profile searches where we are limiting the results to a group.

Filing an issue here: http://issues.civicrm.org/jira/browse/CRM-7639

[mdlueck]

@lcdweb: Do you know would that INNER JOIN being present NOT show pending deletes?

I guess this little bit:

Code: [Select]

AND subtype.is_active = 1 AND parent.is_active = 1might be that specific qualification.

[lcdweb]

no -
trashed records are designated by is_deleted = 1 in the contacts table

[mdlueck]

Then this is a different situation. so two bugs in Profiles which search I guess.

[mdlueck]

Our application team just reminded me of this possible bug with Civi.

The total number of records between "Find Contacts" and entering the specific group name in the "in" pick list and a Profile which is also associated with that same group name do not return the same number of matching records.

I recalled my finding a SQL discrepancy between the SQL those two UI paths end up executing. The traces are yet attached to this thread.

Could someone from the Civi dev team kindly take a quick peek at the SQL traces and see if that indeed is where the total record count discrepancy comes from? Thank you!