API call filtering contacts by email1 returns contacts with deleted mail addresses

[DaRealWorm]

Issue

When making an API call to the V8 Json API for contacts filtering by email1 field eg [...]/V8/module/Contacts?filter[email1][eq]=test@example.com contacts are returned that do no currently have this address but had it in the past and is now deleted. The resulting query is SELECT contacts.id FROM email_addresses JOIN email_addr_bean_rel ON email_addresses.id = email_addr_bean_rel.email_address_id JOIN contacts ON contacts.id = email_addr_bean_rel.bean_id where ((email_addresses.email_address = 'test@example.com') AND contacts.deleted = '0') AND contacts .deleted=0 LIMIT 0,20. This will return all contacts that currently have or ever had a relationship to this e-mail address. Only deleted contacts are excluded but no deleted email_addr_bean_rels.

Expected Behavior

Only contacts currently having an active (deleted = 0) relationship to the e-mail address should be returned.

Actual Behavior

Contacts whose relationship to this address has been deleted will still be found and returned.

Possible Fix

Including and email_addr_bean_rel.deleted = 0 in the query. Can be included in Api/V8/Service/ModuleService.php where the Email where clause is being constructed.

Steps to Reproduce

1. enter e-mail address (eg. test@example.com) in contact
2. remove address from contact
3. query Json API [...]/V8/module/Contacts?filter[email1][eq]=test@example.com
4. contact will be returned although it does not contain the address

Context

I was trying to find a contact by e-mail address and got one that obviously did not contain the searched address. After checking the query I found that the contact previously had the address but was removed later.

Your Environment

• SuiteCRM 7.12.5
• PHP 7.3.29
• MySQL 5.7.36
• Red Hat Enterprise Linux Server release 7.9
• Postman v9.20.3

[samus-aran]

Seems fairly logical the fix. I dunno if this has any play in the relationship between this bug and theirs https://github.com/salesagility/SuiteCRM/issues/9147

[krzaychoos]

Unfortunately, their bug #9147 does not resolve Contacts issue. Contacts with deleted email addresses are still found via API V8 queries. Has anyone found a solution to this problem?

[SuiteBot]

This issue has been mentioned on SuiteCRM. There might be relevant details there:

https://community.suitecrm.com/t/api-returning-duplicate-records-on-email1-filter/88399/8

[krzaychoos]

Exactly! THX!

[chris001]

Would someone like to make a PR to fix this issue, from the fixed SQL provided in this reply to the forum post?: https://community.suitecrm.com/t/api-returning-duplicate-records-on-email1-filter/88399/4