Post originally written by sugarraagaard.

Our sample endpoint is going to go beyond the filter API and give us a list of accounts ordered by the number of open cases.

1. Deciding on an endpoint location

This is just about the most important step in adding a new endpoint. The URL of an endpoint should be created using RESTful ideas and placed in the virtual directory tree in a way that will make it easy for developers too see the original intent of this API.

Since this endpoint is just going to be reading data let's make it a "GET" request.

Because we are dealing primarily with account data we'll throw it in "/Accounts".

To finish it off we'll name the endpoint "at_risk".

So with it all together our endpoint is "GET /Accounts/at_risk", now we could have our URL set to anything else but with a descriptive name and using the correct HTTP verb of GET it will help any other developers coming across calls to this endpoint to better understand what we are asking for.

2. Creating the endpoint class

The REST service looks in a lot of locations for endpoint classes:

• clients/:platform/api/*
• modules/:module/clients/:platform/api/*
• custom/clients/:platform/api/*
• custom/modules/:module/clients/:platform/api/*

Since we are adding a custom accounts endpoint we'll create a new class "AtRiskApi" and put it in the file "custom/modules/Accounts/clients/base/api/AtRiskApi.php". It is important to name the file so that it is the same as the class name except with .php at the end otherwise the REST service won't find our class.

To get this class so it is listening for a specific URL we need to add a function ->registerApiRest(). We are setting the path to array('Accounts', 'at_risk') and set the pathVars to array('', '') to represent "Accounts/at_risk" and not bring any part of the URL in to our array of arguments. If we wanted to match against a wildcard to look at the at risk profile for a single account record for example we could add a path of array('Accounts', '?', 'at_risk') and a pathVars of array('', 'id', '') which would let us listen for "Accounts/*/at_risk" and would take the second part of the path and populate the id element of our arguments with it.

Next we will actually add a function, setting the method in our register array to getAtRisk lets the REST API know to call that method in this class. We'll keep this method simple for now and just have it return 'burgers' just so we can tell it is working right away. These methods just need to return data and the REST API will take care of all the json encoding for you.

Finally we add a little line in the shortHelp giving a quick description of what this endpoint is for. We're leaving the longHelp out of this little demo but if you are building endpoints for real be sure to add some detailed documents there.

So, after everything is all said and done, here's what our little class looks like:

AtRiskApi.php

<?php

class AtRiskApi extends SugarApi
{
 // This function is only called whenever the rest service cache file is deleted.
 // This shoud return an array of arrays that define how different paths map to different functions
 public function registerApiRest() {
 return array(
 'getAtRisk' => array(
 // What type of HTTP request to match against, we support GET/PUT/POST/DELETE
 'reqType' => 'GET',
 // This is the path you are hoping to match, it also accepts wildcards of ? and <module>
 'path' => array('Accounts', 'at_risk'),
 // These take elements from the path and use them to populate $args
 'pathVars' => array('', ''),
 // This is the method name in this class that the url maps to
 'method' => 'getAtRisk',
 // The shortHelp is vital, without it you will not see your endpoint in the /help
 'shortHelp' => 'Lists at risk accounts in the system',
 // The longHelp points to an HTML file and will be there on /help for people to expand and show
 'longHelp' => '',
            ),
        );
    }
 
 function getAtRisk($api, $args)
    {
 // Start off with something simple so we can verify the endpoint is registered.
 return 'burgers';
    }
}

3. Taking it for a test drive

Let's do a GET request for /rest/v10/Accounts/at_riskcurl -X GET -H OAuth-Token:some-token http://localhost/burgers/rest/v10/Accounts/at_risk

And here is what we get back:

response.json

{
"error": "not_found",
"error_message": "Could not find record: at_risk in module: Accounts"
}

Hey, what gives? First things first let's check to see if it registered correctly by looking for the endpoint definition in /help, navigate over to /rest/v10/help in your browser and look for it. Not there? didn't think so.

We added the class and it didn't load. Since the REST API looks for so many files in so many directories we have to add some heavy duty caching in order to speed up the url lookup on every single request. In order for changes in endpoint definitions to show up we need to login as an admin and run quick repair and rebuild.

After quick repair, let's check /rest/v10/help again and you should see a line like this:

So let's try that request again.curl -X GET -H OAuth-Token:some-token http://localhost/burgers/rest/v10/Accounts/at_risk

Now we get back the correct response:

response.json

"burgers"

4. Fetching the data

While having a new URL that says "burgers" is pretty fancy I think we can accomplish more. While there are many ways to fetch and return this data I want to show you the preferred way to do it in Sugar 7.

First things first we need to start off by using SugarQuery. Let's get a seed bean going by fetching a new bean from the bean factory. We pass that through to the SugarQuery ->from() method to let SugarQuery know we will be querying off of the Accounts module. We'll limit our result set to just ID's by adding ->select('id') and then limit our rows to just the first five by adding ->limit(3). From here we can just have it return the results of the ->execute() call and see what that gets us.

Now our getAtRisk function looks like this:

getAtRisk.php

<?php
function getAtRisk($api, $args)
{
 $seed = BeanFactory::newBean('Accounts');

 $q = new SugarQuery();
 // Set from to the bean first so SugarQuery can figure out joins and fields on the first try
 $q->from($seed);
 // Adding the ID field so we can validate the results from the select
 $q->select('id');
 $q->limit(5);

 // Return the raw SQL results through the API
 return $q->execute();
}

and when we make that same GET request to Accounts/at_risk we get back:

response.json

[
    {
"id": "160a729d-f808-a046-e901-5315ffa4aa2a"
    },
    {
"id": "2b0f91be-4392-8de5-896e-5315ffc814da"
    },
    {
"id": "2e890e9a-ca34-6b00-860a-5315ffdb0f71"
    }
]

Okay so now we have some simple SQL being run and are returning the result set. How about we add some more complex logic here so we actually fetch the results we want. To start things off let's join in the cases by adding this "$caseAlias = $q->join('cases')->joinName();". It's nice that we just need to use the link field to add a join and everything else is handled by SugarQuery. SugarQuery also understands that we have to go beyond it's abilities every once in a while, so we need to add a ->fieldRaw() call to fetch the count and then an ->orderByRaw() to properly sort them. We have to use the Raw versions of the functions because neither of those columns are defined in the field_defs for the modules. The ->groupBy() call just needs to group by the account ID so that is simple. Finally the ->where()->notIn() is there so we only fetch related cases that aren't resolved, no need to quote elements here because SugarQuery will handle that for us.

Added all together it looks like this:

GetAtRisk.php

<?php
function getAtRisk($api, $args)
{
 $seed = BeanFactory::newBean('Accounts');

 $q = new SugarQuery();
 // Set from to the bean first so SugarQuery can figure out joins and fields on the first try
 $q->from($seed);
 // We need to save the alias of any join because they are auto-generated (and different if you are on some databases)
 $caseAlias = $q->join('cases')->joinName();
 // Adding the ID field so we can validate the results from the select
 $q->select('id');
 // fieldRaw will let us run raw SQL in the select, be sure to quote anything you are getting from users.
 // The second argument is the field alias so we can tell what will be coming out of the other end of the query
 $q->select->fieldRaw("COUNT(".$caseAlias.".id)","case_count");
 // We need to use orderByRaw here instead of just orderBy because case_count isn't part of any vardefs
 $q->orderByRaw('case_count');
 $q->groupBy('id');
 // the ->where() gives us a where object, there are a lot of operators that work on that (including ->and() and ->or())
 $q->where()->notIn($caseAlias.'.status', array('Closed', 'Rejected'));
 $q->limit(3);

 return $q->execute();
}

Once again let's hit Accounts/at_risk and see what we get:

response.json

[
    {
"id": "f4225353-54a0-502e-409a-5315ffa656ab",
"case_count": "5"
    },
    {
"id": "c4024e67-dee3-553d-0040-5315ff8f2fd1",
"case_count": "5"
    },
    {
"id": "ab6b6784-38cc-077a-fda5-5315ff228fcf",
"case_count": "4"
    }
]

Looking good! Now we are getting the data we need how about we make it look nice for the javascript client that needs it?

5. Formatting the data

To format the data first we have to figure out what to format. Most endpoints accept the argument fields to specify which fields they want returned from the api and we'll keep up that tradition here with some simple parsing of the $args array.

Next up we want to convert the results of the query into beans so they can be properly formatted. Previously you would have to perform the PHP equivalent of banging two rocks together to make fire by manually fetching the rows and creating beans and populating them via ->populateFromRow(). Fortunately we are introducing a helper function in SugarBean named ->fetchFromQuery() to help automate and centralize this process, so we'll just call that here. We need to pass ->fetchFromQuery() the option of returnRawRows because we need to populate the case_count field manually because it doesn't exist in any of the field definitions.

With the beans returned from ->fetchFromQuery() we strip out the raw rows from the result set and then pass the remaining beans through to ->formatBeans() so that our returned results look the same as every single other API call. After we get the results back as an array from ->formatBeans() we loop through the results and jam the case_count in there.

So with all that, here's what our final method looks like:

getAtRisk.php

<?php
function getAtRisk($api, $args)
{
 $seed = BeanFactory::newBean('Accounts');

 $q = new SugarQuery();
 // Set from to the bean first so SugarQuery can figure out joins and fields on the first try
 $q->from($seed);
 // We need to save the alias of any join because they are auto-generated (and different if you are on some databases)
 $caseAlias = $q->join('cases')->joinName();
 // Adding the ID field so we can validate the results from the select
 $q->select('id');
 // fieldRaw will let us run raw SQL in the select, be sure to quote anything are getting from users.
 // The second argument is the field alias so we can tell what it will be coming out the other end of the query
 $q->select->fieldRaw("COUNT(".$caseAlias.".id)","case_count");
 // We need to use orderByRaw here instead of just orderBy because case_count isn't part of any vardefs
 $q->orderByRaw('case_count');
 $q->groupBy('id');
 // the ->where() gives us a where object, there are a lot of operators that work on that (including ->and() and ->or())
 $q->where()->notIn($caseAlias.'.status', array('Closed', 'Rejected'));
 $q->limit(3);

 // Let's parse the field array like formatBeans down below
 if (empty($args['fields'])) {
 $args['fields'] = array();
    } else if (!is_array($args['fields'])) {
 $args['fields'] = explode(',', $args['fields']);
    }

 // Run the new ->fetchFromQuery() call to get beans out of a query, get the raw rows for non-vardef fields
 $accountBeans = $seed->fetchFromQuery($q, $args['fields'], array('returnRawRows' => true));

 // The normal beans are in there by id, the raw rows are returned in their own element
 // Let's strip that out so we don't try to apply sugarbean code to it.
 $rows = $accountBeans['_rows'];
 unset($accountBeans['_rows']);

 // Part of SugarApi, this will format our list of beans like all of the rest of the API's
 // Consistency is good
 $accounts = $this->formatBeans($api, $args, $accountBeans);

 // Since case_count isn't part of the vardefs, we have to populate it manually
 foreach ($accounts as &$account) {
 $account['case_count'] = (int)$rows[$account['id']]['case_count'];
    }

 return $accounts;
}

And when we finally call the Accounts/at_risk, here is what we get:curl -X GET -H OAuth-Token:some-token http://localhost/burgers/rest/v10/Accounts/at_risk?fields=id,name,date_modified

response.json

[
    {
"id": "f4225353-54a0-502e-409a-5315ffa656ab",
"name": "A Fridge Too Far",
"date_modified": "2014-03-04T16:28:59+00:00",
"_acl": {
"fields": {}
        },
"_module": "Accounts",
"case_count": "5"
    },
    {
"id": "c4024e67-dee3-553d-0040-5315ff8f2fd1",
"name": "That's Improv-able",
"date_modified": "2014-03-04T16:28:59+00:00",
"_acl": {
"fields": {}
        },
"_module": "Accounts",
"case_count": "5"
    },
    {
"id": "ab6b6784-38cc-077a-fda5-5315ff228fcf",
"name": "Annie Get Your Gum",
"date_modified": "2014-03-04T16:28:59+00:00",
"_acl": {
"fields": {}
        },
"_module": "Accounts",
"case_count": "5"
    }
]

6. All done!

That's all, I hope this clears how to add your own endpoint to Sugar 7. Along with some helpful tips on how to use a combination of SugarQuery, ->fetchFromQuery() and ->formatBeans() to create easy and standardized code for returning data from the API. Add a comment if you have questions.