API Returning Duplicate Records on email1 filter

💬 General Discussion
1

For some unknown reason the API is returning multiple duplicate records (sometimes up to three (3)) with the exact same record data when doing a filter on “email1”

This has been verified in both native API calls and in the PySuiteCRM module

If I post something like this with curl:

url = “https://” + host + “/Api/V8/module/Contacts?filter[email1][eq]=target@domain.com”

or with PySuiteCRM using

result = suitecrm.Contacts.get(email1=‘target@domain.com’)

And there is absolutely only ONE Contact with that email address, the API returns duplicates.

If I filter on first_name & last_name it returns a single record so it appears to be related somehow to the way email addresses are linked in?

2

HA! Just plugged in a patch to log the query in ModuleService.php at around line 175 (found by doing a grep -ir on email1)

$myfile = file_put_contents(‘suitecrm.sql.log’, $query.PHP_EOL , FILE_APPEND | LOCK_EX);

The query is totally returning the same ID twice!

MariaDB [suitecrm]> 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 = 'target@domain.com') AND contacts.deleted = '0') AND contacts .deleted=0;
+--------------------------------------+
| id                                   |
+--------------------------------------+
| 7141c3fb-c98b-11e2-d577-5e0e3c2d6149 |
| 7141c3fb-c98b-11e2-d577-5e0e3c2d6149 |
+--------------------------------------+
2 rows in set (0.01 sec)
3

I re-wrote the query into natural joins just to get my head wrapped around it and I found the issue. The query is NOT including the bean_module

MariaDB [suitecrm]> SELECT c.id,eabr.bean_module FROM contacts c, email_addresses ea, email_addr_bean_rel eabr WHERE ea.id = eabr.email_address_id AND c.id = eabr.bean_id AND ea.email_address = 'target@domain.com' AND c.deleted='0';
+--------------------------------------+-------------+
| id                                   | bean_module |
+--------------------------------------+-------------+
| 7141c3fb-c98b-11e2-d577-5e0e3c2d6149 | Contacts    |
| 7141c3fb-c98b-11e2-d577-5e0e3c2d6149 | Leads       |
+--------------------------------------+-------------+
2 rows in set (0.00 sec)
4

JUST FOUND ANOTHER PROBLEM WITH ‘deleted’ not being tested in email_addr_bean_rel

This seems to fix it, at least as far as the way I think it should work – unless someone has some better ideas?

ModuleService.php

            // Email where clause
            $fromQuery
#                = 'FROM email_addresses JOIN email_addr_bean_rel ON email_addresses.id = email_addr_bean_rel.email_address_id JOIN '
                 = 'FROM email_addresses JOIN email_addr_bean_rel ON email_addresses.id = email_addr_bean_rel.email_address_id AND email_addr_bean_rel.bean_module = \'' . $selectedModule . '\' AND email_addr_bean_rel.deleted=0 JOIN '
5

I will pile on here and say its kind of silly to offer an “email” field that doesn’t work at all in the Filters which evidently rely on email1 or email2

This should just be generically solved for “email” and remove the fragile dependency on having to use enumerated versions

6

I guess you just found a bug in the API code - this can be tricky. The email1 “pseudo-field” is a hacked work-around to help people avoid the complexities of the many-to-many relationships of email_addresses module. Sometimes it backfires.

1 Like
7

FWIW, the way I’ve been handling it in our API’s is by using singular names for one value and plurals for arrays, so for example:

“email” : “target@domain.com”,

“emails” : [ ]

if email has other attributes i would trend towards:

"email : {“address” : “target@domain.com”, “type” : “work”, “preferred” : “yes” }

Not trying to boil the ocean here, just finally got enough free time to make testing some integration with SuiteCRM a priority for us and will likely stumble upon other idiosyncratic behaviors as I proceed. …

1 Like
8

Your proposal seems to actually solve this bug #9606 …at least for me :smiley: THX!