Help on complex queries with the SuiteCRM rest api

💬 General Discussion
1

Using the REST API I want to get Target Lists that have more than 2 Targets In List. I noticed that in the database structure the prospect_lists table, which corresponds to Target Lists, does not have an entry_count field, which would make the query easier.
I tried the following API call rest data, but it’s giving the “Access Denied” error.

POST /suitecrm/service/v4_1/rest.php?input_type=json&response_type=json&method=get_entry_list&rest_data={“session”:“session_id”,“module_name”:“ProspectLists”,“query”:"(prospect_lists.id+in+(SELECT+prospect_lists.id+FROM+prospect_lists+join+users+on+prospect_lists.assigned_user_id=users.id+group+by+users.id+having+count(*)>=1))",“order_by”:"",“offset”:0,“select_fields”:[],“link_name_to_fields_array”:[],“max_results”:100,“deleted”:0,“favorites”:false}

In phpmyadmin, as an SQL query the query is returning the correct result, but it is necessary for me to use the REST API.
How can I obtain the ‘Targets in List’ count? Is there a way to run “complex” queries like the one i’ve written in the rest data?

Any help would be greatly appreciated.

2

There is code to “sanitize” whatever comes through the API, for security reasons, I’m guessing some part of that query is getting caught up in those checks.

You could extend the API to add your own method/endpoint and then call that.

Or you could add the field with member count in the Target Lists, and contribute that feature to the product, there have been other people asking for it. Any list view showing Target Lists should show target counts, it would be really helpful…