Query API using v5 PHP SDK

Hello and welcome to another week of Kaizen!

In our previous posts, we have already covered in detail about Query API, when to use them and their supported fields and operators with examples. Please refer to COQL API [Part I] and COQL API [Part II] for more details. In this week's post, we will discuss in detail how to use Query API in Zoho CRM PHP SDK. Please note that this article holds true for PHP SDK supporting version 5 of Zoho CRM APIs.

COQL (CRM Object Query Language) is a powerful query language based on SQL syntax that allows users to write their own queries and fetch records using module API names and field API names. Use Query API when you want to query for a module's data and/or it's look up related data using various comparators, or for records that fall into a custom view without actually creating one, or to query for records from cross-modules (linked via lookup field).

How to construct a simple Query?

Begin with the SELECT clause
Specify the fields you want to fetch.
Use the FROM clause to specify the module you are querying.
Use the WHERE clause to apply conditions and filter the results based on specific criteria.
Specify the conditions.
Utilize additional clauses such as GROUP BY, ORDER BY, LIMIT, and OFFSET to further customize the query results if needed.

For example,

SELECT Last_Name, First_Name, Mobile FROM Leads WHERE (Lead_Status = 'Not Contacted') ORDER BY Last_Name DESC LIMIT 5 OFFSET 10;

COQL API using PHP SDK

To make a query, use the following code snippet in your code.

// Instantiate QueryOperations class

$queryOperations = new QueryOperations();

// Create a new BodyWrapper instance to set the query

$bodyWrapper = new BodyWrapper();

// Define your COQL select query

$selectQuery = "give_your_select_query_here";

// Set the select query in the body wrapper

$bodyWrapper->setSelectQuery($selectQuery);

// Execute the query and get the response

$response = $queryOperations->getRecords($bodyWrapper);

Let us explore in detail with examples how to use COQL for various field types and operators.

I. Text and Picklist Fields

The supported operators for Text and Picklist fields are =, !=, like, not like, in, not in, is null, is not null.

Sample code using =, like, in operators

<?php

use com\zoho\crm\api\query\BodyWrapper;

use com\zoho\crm\api\query\QueryOperations;

require_once "vendor/autoload.php";

class GetRecords

{

public static function initialize()

{

// add initialisation code

// refer to this post for details and examples

}

public static function getRecords1()

{

$queryOperations = new QueryOperations();

$bodyWrapper = new BodyWrapper();

$selectQuery =

"SELECT First_Name, Last_Name FROM Leads WHERE (((Lead_Status = 'Attempted to Contact') and (Company like '%zylker%')) and Lead_Source in ('Employee Referral', 'Advertisement')) LIMIT 5 OFFSET 10";

$bodyWrapper->setSelectQuery($selectQuery);

$response = $queryOperations->getRecords($bodyWrapper);

//Add your code to handle the response received in $response. To know more, refer to this sample code.

}

GetRecords::initialize();

GetRecords::getRecords1();

Please note that the like operator is used for pattern matching, and is used for starts_with, ends_with, and contains operators. In this example query, LIKE operator with the pattern %zylker% is used to find records where the Company field contains the substring zylker anywhere within its value. The % symbol represents any number of characters (including none) before or after the pattern zylker. % is the only supported wildcard character for Zoho CRM Query APIs.

II. Lookup Fields

The supported operators for Lookup fields are =, !=, in, not in, is null, is not null.

Sample Query using !=, is not, not in, is not null

<?php

use com\zoho\crm\api\query\BodyWrapper;

use com\zoho\crm\api\query\QueryOperations;

require_once "vendor/autoload.php";

class GetRecords

{

public static function initialize()

{

// add initialisation code

// refer to this post for details and examples

}

public static function getRecords1()

{

$queryOperations = new QueryOperations();

$bodyWrapper = new BodyWrapper();

$selectQuery = "SELECT Last_Name, First_Name, Full_Name, Account_Name, Owner from Contacts where (((Account_Name.Account_Name != 'Zylker') and (Reporting_To is not null)) and Vendor_Name.Vendor_Name not in ('Skytech','SR'))";

$bodyWrapper->setSelectQuery($selectQuery);

$response = $queryOperations->getRecords($bodyWrapper);

//Add your code to handle the response received in $response. To know more, refer to this sample code.

}

GetRecords::initialize();

GetRecords::getRecords1();

III. Date, DateTime, Number, Currency Fields

=, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null are the supported operators for these fields.

Sample Query using between, <, >, >=

<?php

use com\zoho\crm\api\query\BodyWrapper;

use com\zoho\crm\api\query\QueryOperations;

require_once "vendor/autoload.php";

class GetRecords

{

public static function initialize()

{

// add initialisation code

// refer to this post for details and examples

}

public static function getRecords1()

{

$queryOperations = new QueryOperations();

$bodyWrapper = new BodyWrapper();

$selectQuery = "SELECT Deal_Name, Amount, Stage, Probability FROM Deals WHERE ((Closing_Date between '2023-01-01' and '2023-03-31') and (Probability < 99)) and ((Amount > 10000) OR (Exchange_Rate >= 5))";

$bodyWrapper->setSelectQuery($selectQuery);

$response = $queryOperations->getRecords($bodyWrapper);

//Add your code to handle the response received in $response. To know more, refer to this sample code.

}

GetRecords::initialize();

GetRecords::getRecords1();

Note :

Use the following formats for Date and DateTime fields.

Date : YYYY-MM-DD (eg: 2023-01-01)

DateTime : YYYY-MMDDTHH:mm:ss+/HH:mm (eg: 2023-05-03T20:39:19+05:30)

IV. Boolean

= is the only supported operator for Boolean Fields. You should provide the value as either true or false.

<?php

use com\zoho\crm\api\query\BodyWrapper;

use com\zoho\crm\api\query\QueryOperations;

require_once "vendor/autoload.php";

class GetRecords

{

public static function initialize()

{

// add initialisation code

// refer to this post for details and examples

}

public static function getRecords1()

{

$queryOperations = new QueryOperations();

$bodyWrapper = new BodyWrapper();

$selectQuery = "select Product_Name, Product_Code from Products where Product_Active = 'true'";

$bodyWrapper->setSelectQuery($selectQuery);

$response = $queryOperations->getRecords($bodyWrapper);

//Add your code to handle the response received in $response. To know more, refer to this sample code.

}

GetRecords::initialize();

GetRecords::getRecords1();

Aggregate Function Support

Aggregate functions in COQL allow you to perform calculations on groups of records in a module. The available aggregate functions are SUM, MIN, MAX, AVG, and COUNT.

SUM is used to sum up the values of an aggregate field in a module.
MAX is used to find the largest value of a field in a module.
MIN is used to find the smallest value of a field in a module.]
AVG is used to find the average value of a field in a module.
COUNT is used to count the number of records in a module satisfying a certain criteria.

This feature allows users to perform complex queries on their data and get insights that would not be possible with simple SELECT queries. For example, you could use aggregate functions to find the total revenue generated by all leads in a month, or the average age of all customers in a region.

<?php

use com\zoho\crm\api\query\BodyWrapper;

use com\zoho\crm\api\query\QueryOperations;

require_once "vendor/autoload.php";

class GetRecords

{

public static function initialize()

{

// add initialisation code

// refer to this post for details and examples

}

public static function getRecords1()

{

$queryOperations = new QueryOperations();

$bodyWrapper = new BodyWrapper();

$selectQuery = "SELECT MIN(Amount), MAX(Amount) FROM Deals WHERE Stage = 'Closed Won'";

$bodyWrapper->setSelectQuery($selectQuery);

$response = $queryOperations->getRecords($bodyWrapper);

//Add your code to handle the response received in $response. To know more, refer to this sample code.

}

GetRecords::initialize();

GetRecords::getRecords1();

This query will return the minimum and maximum deal amounts for all deals that have been closed won.

In this guide, we've explored how to use the Query API within Zoho CRM using PHP SDK effectively. By employing COQL, you can create intricate queries to extract specific data from modules, apply various operators, and even utilize aggregate functions for deeper insights. Whether you're seeking lead details, complex filters, or aggregate calculations, Query API empowers you to interact with your CRM data seamlessly.

If you have any queries, let us know the comments below, or send an email to support@zohocrm.com. We would love to hear from you. Your commitment to Kaizen drives us forward!!

Stay tuned for more useful posts in the series!

Access your files securely from anywhere

Zoho Developer Community

New to Zoho LandingPage?

Access Zoho LandingPage

Zoho LandingPage Resources

New to Zoho Survey?

Access Zoho Survey

Latest Feature Updates

Explore our Pricing & Plans

Zoho Survey Resources

Android Mobile Surveys

Insurvey Blogs

Insight Blogs

Tips & Tricks

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Bigin?

Signup Now

Access Bigin

Latest Feature Updates

Zoho Desk Resources

Desk Community Learning Series
Digest
Functions
Meetups
Kbase
Resources
Glossary
Desk Marketplace
MVP Corner
Word of the Day

Tout forum

France ZUGs

Zoho SalesIQ Resources

Topic Participants
Anu Abraham

Sticky Posts
Kaizen#162 : Add Tags and Open Pre-filled Email Drafts via Client Scripts
Hello everyone! Welcome to another informative Kaizen post. In this post, let us see how to accomplish the following using Client Script. 1. How to auto-tag a record based on field update? 2. How to Open a pre-filled email draft This post will provide
Client Script | Update - Introducing Commands in Client Script!
Have you ever wished you could trigger Client Script from contexts other than just the supported pages and events? Have you ever wanted to leverage the advantage of Client Script at your finger tip? Discover the power of Client Script - Commands! Commands
Extension Pointers - JS SDK series #4: Managing data from a widget using ZOHO.CRM.HTTP
Handling and managing the data between two applications is a major factor for an efficient business. There are a variety of ways to handle data between Zoho CRM and other third-party applications. You can use deluge CRM tasks, create connectors, use API
Kaizen #152 - Client Script Support for the new Canvas Record Forms
Hello everyone! Have you ever wanted to trigger actions on click of a canvas button, icon, or text mandatory forms in Create/Edit and Clone Pages? Have you ever wanted to control how elements behave on the new Canvas Record Forms? This can be achieved
Extension pointers for integrating Zoho CRM with Zoho products #8: Upload and manage Zoho Workdrive folders and files from within Zoho CRM
Keeping records on your customers and business prospects is essential for tracking data, conducting follow-ups, and running a business smoothly. When you use two separate applications, and store relevant data in each, checking and tracking data becomes