SK based on DB Entity includes contacts from the trash even when not specified as criteria
Overview
When DB Entity is used as the 'search for' entity, it includes contacts from the trash even if not specified in the criteria of the original SK where the DB Entity display was constructed.
This is the original post in Mattermost.
Reproduction steps
- To test this you need to have some deleted contacts in your trash.
- Add a new SK and do not change anything in the compose window (entity Contacts, everything default).
- Add a DB Entity display to the SK you created in step 1 and preview it
- Save the SK and clear the cache (I'm finding a have to clear the cache a 2-3 times)
- Add a new SK
- As the first search for entity, choose the DB Entity you created in step 2 and view the results
Current behaviour
The results returned for the child SK based on the DB Entity are higher than the results returned for the parent (first) SK. And the table created for the SK DB Entity the same number as shown in child SK. Note the result count in each of the following images. I have two contacts in the trash.
The SK created in step 1 (the parent for the DB Entity display - Basic Contact Search) has 201 results.
The SK where the DB Entity (Basic Contact Search Child) created in step 1 is used as the Search for entity has 203 results.
If I add the following where clause to the parent SK (Basic Contact Search):
WHERE (`a`.`is_deleted` = "0")
I get the correct result in my SK (Basic Contact Search Child) that is based on the DB Entity. The 201 results matches the original SK (Basic Contact Search).
Expected behaviour
One should not be required to add the where clause to exclude the contacts in the trash. It seems as though that should be the default behaviour.
Environment information
- Browser: Firefox 59.0.1/Chrome 78.0.3904/Safari 13
- CiviCRM: 5.69.2
- PHP: 8.0
- CMS: Drupal 7.99
- Database: MariaDB 10.6
- Web Server: Apache 2.4