API-Call with filter: Mysqli_query failed

SuiteCRM Forum - English Language
1

SuiteCRM Version 7.11.13
PHP 7.3.17
MariaDB 10.1.44

Hello,

when I call the API with Oauth2 and try to filter I get following error:
Query Failed: SELECT COUNT(*) AS cnt FROM contacts WHERE contacts.email = ‘beans.sugar.sales@example.cn’ AND contacts.deleted = ‘0’: MySQL error 1054: Unknown column ‘contacts.email’ in ‘where clause’

Calls without filter work. The last errors of this kind are in 2018 and long solved. Who has an idea how to fix this?

Thanks and best regards
Sven

PS: API Reference:

2

I followed up my research and what works are the colums that actually exist like “first_name” in Contacts and “name” in Accounts.

E-Mails are stored in a separate table “email_addresses” though. Thus, the API cannot find the column in the contacts table but must find it in some sort of join. (Which has been implemented in API Output as well as GUI Search)

So is it not working or is it just not implemented in API yet?

3

Hey @Endymion,

I know there’s a fix for email1 value coming within the next major.

Using just

{{suitecrm.url}}/V8/module/Contacts?

You are able to see all of the fields and it appears those like email and email 2 aren’t populated and email1 I belive in current versions cant be filtered by. If you take a look at the relationships however you are able to find the related email.

{{suitecrm.url}}/V8/module/Contacts/{{Contact ID}}/relationships/email_addresses/

Let me know if you have any trouble using the method mentioned.

4

Interesting. I followed your suggested path. It points to a module, which I didn’t know of beforehand:

EmailAddresses
querying {{suitecrm.url}}/V8/module/EmailAddresses/{{Email ID}} gives me the desired result.

I hope for a real fix in the next release, but I learned a ton about the architecture with this + I will hack a temporary workaround together.

I really love to work with your well structured module-based architecture. It is super intuitive and user friendly.

Thanks a bunch, Mac-Rae

1 Like
5

Following up on this:
When I work my way back, I can query EmailAddresses and find my email address example@business.com.

But how can I find the corresponding entry in Contacts, as there is no backwards relationship?

6

Still no fix on this ?

I got this exact same error, but the API filter work lol

Im filtering a field in a form to check if the client is already in the database of suitecrm, the filter work like a charm, it just every call create error in the log …

The call look like Api/V8/module/Accounts?filter[siren_c][eq]=‘XXXX’

In generate in suitecrm.log :
Tue Apr 5 12:14:29 2022 [71911][1][FATAL] Mysqli_query failed.
Tue Apr 5 12:14:29 2022 [71911][1][FATAL] Query Failed: SELECT COUNT(*) AS cnt FROM accounts WHERE (accounts_cstm.siren_c = ‘XXXX’) AND accounts.deleted = ‘0’: MySQL error 1054: Unknown column ‘accounts_cstm.siren_c’ in ‘where clause’

The error are created everytime, if the filter return value or not. So its pretty much a bug

So its not really a big issue but this form is used a lot so it generate a bunch of useless line in the suitecrm.log.

I hope someone can help, if needed I will create an issue