Constructing a COQL Query

The COQL query is built using the SELECT statement followed by the optional clauses such as WHERE, FROM, ORDER BY, LIMIT, and OFFSET. In general, the SELECT query in SQL uses column name and table name for querying data. While the SELECT query in COQL uses field_api_name instead of column name and module_api_name instead of table name for fetching data from Bigin.

The COQL query uses the following syntax:

SELECT {list of comma-separated field_api_names}
FROM {module_api_name}
WHERE {field_api_name} {comparator} {value} {logical_operator} {field_api_name} {comparator} {value}
ORDER BY {field_api_name} ASC/DESC
LIMIT {limit} OFFSET {offset}

The COQL query contains the following components:

• SELECT - Represents the fields you want to retrieve from the module. You can provide a list of comma-separated field API names. These field API names correspond to specific fields of the module. Check Get meta data for fields in a specific module for field API names.
• FROM - Represents the module you want to query. You can provide module API name that identifies the module from which you want to fetch data. Check Get all modules for module API names.
• WHERE - Determines the criteria to filter the results. You can provide field API names, comparators, and corresponding values to define the filtering criteria. Multiple criteria can be associated using logical operators like AND and OR.
• ORDER BY - Allows you to sort the results based on a specific field. You can provide field API name and specify whether the sorting should be in ASC (ascending order) or DSC (descending order).
• LIMIT - Represents the maximum number of records you want to retrieve in a single query. The default value is 200.
• OFFSET - Represents the starting position of the retrieved records. It navigates through the results by skipping a specified number of records. The default value is 0.

Let's consider the following example:

To fetch contacts with the Last_Name "Watson" and Mailing_Zip "08014", sorted in ascending order by First_Name, and limited to 5 records by skipping first 10 records, the query would be:

SELECT First_Name, Last_Name, Mailing_Zip
FROM Contacts
WHERE Last_Name = 'Watson' AND Mailing_Zip = '08014'
ORDER BY First_Name ASC
LIMIT 5 OFFSET 10

Field data types and their supported comparators:

Wildcard Character for LIKE and NOT LIKE operators

The supported wildcard character in COQL queries is %. It can be used with the LIKE operator to achieve functionality similar to the contains, starts_with, and ends_with operators. For example, %sales queries for field values ending with sales, sales% queries for the field values starting with sales, and %sales% queries for the field values containing sales.

For example:

To fetch products with a price that contains the value "$100" and belongs to the categories "Electronics" or "Appliances", sorted in descending order by price, and limited to 20 records, the query would be:

SELECT Product_Category, Unit_Price
FROM Products
WHERE Unit_Price like '%$100%' AND Product_Category in ('Electronics', 'Appliances')
ORDER BY Unit_Price DESC
LIMIT 20 OFFSET 0

COQL Queries support JOINS

COQL supports queries to fetch records from multiple Bigin modules using join operations. Lookup fields in modules allow you to construct queries using joins for fetching related information.

Consider the following Contacts table where Account_Name is the lookup field that represents the Company Name associated with the contact:

Consider the following Companies table:

From above tables, to fetch contacts associated with the Account_Name "Acme Industries" and Billing_City "NYC", sorted in ascending order by Full_Name of the contact, the COQL query would be:

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

In COQL query, a join is created with the dot (.) notation using the lookup field.

To fetch the owner name of the company associated with a contact using lookup field Account_Name, the COQL query would be:

SELECT Last_Name, Account_Name.Owner, Account_Name.Owner.Account_Name
FROM Contacts
WHERE Last_Name is not null AND Account_Name.Owner.Account_Name is not null

In the output of the above query, Account.Owner fetches the ID of the owner and Account_Name.Owner.Account_Name fetches the name of the owner.