Join 2 tables

SugarQuery offers handy wrappers to join across module, using the "link fields" to automatically relate two modules's SQL tables. You can read more about it here: https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_10.1/Data_Framework/Database/SugarQuery/#Relationships

Restrain yourself from using direct SQL queries. While they might be "quicker" to put together (if you are not familiar with SugarQuery), it opens your solution up to problems (eg: SQL injection, portability across databases, missing a relevant parameter on the query to name a few)

from where i download sugarQuery?

Rana Awais, your original question was about joining MySQL tables, so that's why I pointed you towards SugarQuery. Based on the question, I thought you wanted to build a custom api or functionality.

SugarQuery is included on Sugar's codebase.

There are many ways to achieve what you want to do. One example can be found here: https://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_10.1/Cookbook/Web_Services/REST_API/PHP/How_to_Fetch_Related_Records/

You could also bulk apis together so that you limit the number of http requests you make.

If you open your instance's /rest/v10/help url, you will get a help page on how to use our api as well.

Enrico Simonetti basically my question is how i can join 2 modules like in MYSQL have 

I want data from 2 modules in single Query

v11

v11

Which Version do you use?

In version 10.1.0 you find that file in ...\include\SugarQuery\SugarQuery.php, which version is shown when you open the about screen in Sugar?

can you please send me your sugarQury.php file
i will place it in my local and do my work on local

Harald Kuske

Sorry, that's not possible as the code is under license restrictions, please look to your code, which version is shown when you open the about screen in Sugar?

version 10

Sorry, it's really important to have the full version number behind "About Sugar SugarCRM Version" to know where the file is located in that version.

From what I understand from the conversation, looks like Rana Awais is only having API access to Sugar, not the codebase itself, is that right Rana?

The SugarQuery.php that Harald and Enrico are talking about - is part of the sugar code base, so if you need an API-only solution, sending the file to you will not help, because you won't be able to run it.

The only solution I see here is the one Enrico suggested - using two API calls.

1.  Get all your 30 records first, then group all the IDs - This is one API call.

2. Instead of iterating the 30 records one by one and calling Sugar 30 times, use one API call with "in()" criteria - this will only make one API call. 

Use the link that Enrico suggested to know how to make "$in" api calls:

POST {{rest_url}}/Reseller/filter

{
  "filter": [
    {
      "id": {
        "$in": [
          "id1",
          "id2",...
          "id30"
        ]
      }
    }
  ]
}

So ultimately your API calls will be reduced from 31 to just two, which will resolve your slowness problems.

From what I understand from the conversation, looks like Rana Awais is only having API access to Sugar, not the codebase itself, is that right Rana?

The SugarQuery.php that Harald and Enrico are talking about - is part of the sugar code base, so if you need an API-only solution, sending the file to you will not help, because you won't be able to run it.

The only solution I see here is the one Enrico suggested - using two API calls.

1.  Get all your 30 records first, then group all the IDs - This is one API call.

2. Instead of iterating the 30 records one by one and calling Sugar 30 times, use one API call with "in()" criteria - this will only make one API call. 

Use the link that Enrico suggested to know how to make "$in" api calls:

POST {{rest_url}}/Reseller/filter

{
  "filter": [
    {
      "id": {
        "$in": [
          "id1",
          "id2",...
          "id30"
        ]
      }
    }
  ]
}

So ultimately your API calls will be reduced from 31 to just two, which will resolve your slowness problems.

Neeraja P Thanks 
that is very understanding solution
appreciate your communicating skill