Query API - An Overview

CRM Object Query Language (COQL) is a query language based on the SQL syntax. It helps the users write their own queries to get records from Vertical Solutions subscriber org by using field API names instead of column names and module API names instead of table names.

You can use this API to filter and fetch records based on certain criteria instead of creating custom views and passing that cvid(custom view ID) as a parameter in your request. Using this API, you can retrieve a maximum of 200 records.

Advantages of COQL

• COQL is based on SQL syntax and is very simple to use.

• No need to create custom views based on certain criteria and pass that cvid in the request. Instead, you can use the COQL Select query with different comparators directly in the input to fetch records that match the criteria.

• The supported COQL comparators cover a wide range of search possibilities. For example, you can use the in comparator to search for records from a list of values which is not possible using the Search Records API.

• The limit clause allows you to set the number of records you want to fetch. You can fetch a maximum of 200 records using this API.

• Easy to join or relate two modules with the help of lookup fields using .(dot)

• No need to specify the alias of the module explicitly when establishing a relation between two modules, as the API names are unique for every module.

How to build a COQL Query?

Use SQL Select query to build a COQL query with module_API_names as table names and field_API_names as column names.

Syntax

SELECT field_api_name1, field_api_name2
FROM base_module_api_name
WHERE field_api_name comparator logical_operator field_api_name comparator
ORDER BY field_api_name ASC/DESC
LIMIT offset, limit

Consider the following example query

SELECT First_Name, Last_Name, Full_Name
FROM Leads
WHERE Last_Name = 'Boyle' and First_name = 'Patricia'
ORDER BY Full_Name
LIMIT 10, 2

The above query will return the record from the Leads module with the specified first name and last name, sorted based on the full name of the leads. The limit is set to 2 and offset is set to 10, meaning that the system skips the first 10 rows and fetches the records from the next two rows. The response is as follows.

{
"data": [
    {
        "First_Name": "Patricia",
        "Full_Name": "Patricia Boyle",
        "Last_Name": "Boyle",
        "id": "554023000000322019"
    }
],
"info": {
    "count": 1,
    "more_records": false
}
}

How to use Joins in COQL?

The SQL Join clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

In COQL, you can establish a join or a relation with the help of lookup fields that relate one module with the other.

Consider the following "Contacts" table where Account_Name is the lookup field that represents the account name associated with the contact.

Consider the following Accounts table.

The SQL query to fetch all contacts associated with the account, ABC Inc and the billing city, NYC, sorted in ascending order based on the full name of the contact will be

SELECT First_Name, Last_Name, Full_Name
FROM Contacts AS C LEFT JOIN Accounts AS A ON (C.id = A.id)
WHERE A.Account_Name = 'ABC Inc' and A.Billing_City = 'NYC'
ORDER BY Full_Name ASC;

The COQL query for the same will be

SELECT First_Name, Last_Name, Full_Name
FROM Contacts
WHERE Account_Name.Account_Name = 'ABC Inc' and Account_Name.Billing_City = 'NYC'
ORDER BY Full_Name ASC;

In the COQL query, a relation is established with the .(dot) using the lookup field.

Consider you have an account lookup in contacts module that points to the Account_Name that the contact is associated with. The Account has a parent account. So, to fetch the parent account name of the account that is associated with the contact, use relations between the modules as below.

SELECT Last_Name, Account_Name.Parent_Account, Account_Name.Parent_Account.Account_Name
FROM Contacts
WHERE Last_Name is not null and Account_Name.Parent_Account.Account_Name is not null

In the above sample, Account_Name.Parent_Account returns the ID of the parent account and Account_Name.Parent_Account.Account_Name returns the name of the parent account. The response is as follows.

{
    "data": [
        {
            "Account_Name.Parent_Account.Account_Name": "Zylker",
            "Last_Name": "Boyle",
            "Account_Name.Parent_Account": {
                "id": "554023000000238121"
            },
            "id": "554023000000310003"
        },
        {
            "Account_Name.Parent_Account.Account_Name": "Zylker",
            "Last_Name": "Patricia",
            "Account_Name.Parent_Account": {
                "id": "554023000000238121"
            },
            "id": "554023000000310012"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

What is the GROUP BY clause?

The "GROUP BY" clause is used to retrieve data from a table and group it based on the given conditions. Grouping records by specifying the field names (field API names) as conditions in a query. It helps you filter and organize the records efficiently, and group the results of a query based on one or more columns as the objects in the response.

Currently, the Query API does not support the distinct keyword to get distinct or unique values for multiple fields of a table. Instead, use the GROUP BY clause to achieve this.

Example:

{
    "select_query": "select Last_Name from Leads where Last_Name is not null group by Last_Name"
}

{	 
    "data": [
        {
            "Last_Name": "Benjamin"
        },
        {
            "Last_Name": "Boyle"
        },
        {
            "Last_Name": "Candy"
        },
        {
            "Last_Name" : "John"
        }
      
    ],
    "info": {
        "count": 4,
        "more_records": false
    }
}

{
	"select_query": "select Last_Name, Account_Name.Parent_Account.Vendor.Product
	from Contacts where Last_Name = 'Boyle1'"
}

{
    "data": [
        {
            "Last_Name": "Boyle1",
            "Account_Name.Vendor.Product": {
                "id": "554023000000377090"
            }
            "id": "554023000000316022"
        },
        ],
    "info": {
        "count": 1,
        "more_records": false
    }
}