Hi all!
I'm using SugarCRM API v11_22 and I'm testing the Users module.
If I run different queries I get different results, sometimes even errors. What I've noticed is a mechanism under the hood that calculates the load of the query and depending on that decides how many rows to return despite the page Size. Let me explain in detail:
I have 48 records on the Users table and I'm using this endpoint XXXX.sugarcrm.com/.../filter
CASE 1
If I try the post data of {"max_num":"10","fields":"id","order_by":"id:ASC"}
it does the pagination fine.
CASE 2
If I try to get all the fields:
{"max_num":"10","fields":"id,user_name,user_hash,system_generated_password,pwd_last_changed,authenticate_id,sugar_login,picture,first_name,last_name,full_name,name,is_admin,external_auth_only,receive_notifications,send_email_on_mention,description,date_entered,date_modified,last_login,modified_user_id,modified_by_name,created_by,created_by_name,title,department,phoneXXXXX","order_by":"id:ASC"}
It throws an error: "500 Internal Server Error" without any error message in the body. PS: the list of fields includes all the fields that are exposed by the /metadata endpoint
CASE 3
If I split the list of columns into 2 different requests I get different results:
Request 1:
{"max_num":"10","fields":"id,user_name,user_hash,system_generated_password,pwd_last_changed,authenticate_id,sugar_login,picture,first_name,last_name,full_name,name,is_admin,external_auth_only,receive_notifications,send_email_on_mention,description,date_entered,date_modified,last_login,modified_user_id,modified_by_name,created_by,created_by_name,title,department,phone_home,phone_mobile,phone_work,phone_other,phone_fax,status,address_street,address_city,address_state,address_country,address_postalcode,usertype,license_type,default_team","order_by":"id:ASC"}
>>I get only 2 pages of data then nextPage =-1
Request 2:
{"offset":"10","max_num":"10","fields":"id,user_name,user_hash,system_generated_password,pwd_last_changed,authenticate_id,sugar_login,picture,first_name,last_name,full_name,name,is_admin,external_auth_only,receive_notifications,send_email_on_mention,description,date_entered,date_modified,last_login,modified_user_id,modified_by_name,created_by,created_by_name,title,department,phone_home,phone_mobile,phone_work,phone_other,phone_fax,status,address_street,address_city,address_state,address_country,address_postalcode,usertype,license_type,default_team","order_by":"id:ASC"}
>>I get all the pages
What can I do to have a consistent behavior?
Is there any endpoint that calculates the query cost and decides the number of rows to get per page or anything else?
This is a very tricky behavior for our application and leads us to the wrong results.
Please let me know the resolution or if you have any questions!
Thanks!