Kaizen #98 - Query API using v5 PHP SDK

Kaizen #98 - 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?

  1. Begin with the SELECT clause
  2. Specify the fields you want to fetch.
  3. Use the FROM clause to specify the module you are querying.
  4. Use the WHERE clause to apply conditions and filter the results based on specific criteria.
  5. Specify the conditions.
  6. 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! 


    • Sticky Posts

    • Kaizen #197: Frequently Asked Questions on GraphQL APIs

      🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Kaizen #198: Using Client Script for Custom Validation in Blueprint

      Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Celebrating 200 posts of Kaizen! Share your ideas for the milestone post

      Hello Developers, We launched the Kaizen series in 2019 to share helpful content to support your Zoho CRM development journey. Staying true to its spirit—Kaizen Series: Continuous Improvement for Developer Experience—we've shared everything from FAQs

    • Kaizen #193: Creating different fields in Zoho CRM through API

      🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • 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

    • Recent Topics

    • Download an email template in html code

      Hello everyone, I have created an email template and I want to download it as html. How can i do that? I know you can do it via the campaigns-first create a campaign add the template and download it as html from there. But what if i don't want to create

    • Attachment is not included in e-mails sent through Wordpress

      I have a Wordpress site with Zeptomail Wordpress plugin installed and configured. E-mails are sent ok through Zeptomail but without the included attachment (.pdf file) Zeptomail is used to send tickets to customers through Zeptomail. E-Mails are generated

    • Upcoming Changes to the Timesheet Module

      The Timesheet module will undergo a significant change in the upcoming weeks. To start with, we will be renaming Timesheet module to Time Logs. This update will go live early next week. Significance of this change This change will facilitate our next

    • Best way to schedule bill payments to vendors

      I've integrated Forte so that I can convert POs to bills and make payments to my vendors all through Books. Is there a way to schedule the bill payments as some of my vendors are net 30, net 60 and even net 90 days. If I can't get this to work, I'll have

    • Cant update image field after uploading image to ZFS

      Hello i recently made an application in zoho creator for customer service where customers could upload their complaints every field has been mapped from creator into crm and works fine except for the image upload field i have tried every method to make

    • Billing Management: #4 Negate Risk Free with Advances

      In the last post, we explored how unbilled charges accumulate before being invoiced. But what happens when businesses need money before service begins? Picture this: A construction company takes on a $500,000 commercial building project expected to last

    • Is there an equivalent to the radius search in RECRUIT available in the CRM

      We have a need to find all Leads and/or Contacts within a given radius of a given location (most likely postcode) but also possibly an address. I was wondering whether anyone has found a way to achieve this in the CRM much as the radius search in RECRUIT

    • Zoho CRM Inventory Management

      What’s the difference between Zoho CRM’s inventory management features and Zoho Inventory? When is it better to use each one?

    • Cannot Enable Picklist Field Dependency in Products or Custom Modules – Real Estate Setup

      Hello Zoho Support, I am configuring Zoho CRM for real estate property management and need picklist field dependency: What I’ve tried: I started by customizing the Products module (Setup > Modules & Fields) to create “Property Type” (Housing, Land, Commercial)

    • Get Workflow Metadata via API

      Is there a way to get metadata on workflows and/or custom functions via API? I would like to automatically pull this information. I couldn't find it in the documentations, but I'm curious if there is an undocumented endpoint that could do this. Moderation

    • Zoho Projects - Q2 Updates | 2025

      Hello Users, With this year's second quarter behind us, Zoho Projects is marching towards expanding its usability with a user-centered, more collaborative, customizable, and automated attribute. But before we chart out plans for what’s next, it’s worth

    • FSM setup

      So we have been tinkering with FSM to see if it is going to be for us. Now is the time to bite the bullet and link it to our zoho books and zoho crm. The help guides are good but it would really help if they were a bit more in depth on the intergrations.

    • Upcoming Updates to the Employees Module in Zoho Payroll (US)

      We've made a couple of updates to the Employees module in Zoho Payroll (latest version of the US edition). These changes will go live today. While creating an employee Currently, the Compensation Details section is part of the Basic Details step, where

    • Possible to Turn Off Automatic Notifications for Approvals?

      Hello, This is another question regarding the approval process. First a bit of background: Each of our accounts is assigned a rank based on potential sales. In Zoho, the account rank field is a drop-down with the 5 rank levels and is located on the account

    • ZOHO Creator subform link

      Dear Community Support, I am looking for some guidance on how to add a clickable link within a Zoho Creator subform. The goal is for this link to redirect users to another Creator form where they can edit the data related to the specific row they clicked

    • Allow Resource to Accept or Reject an Appointment

      I have heard that this can be done, is there any documentation on how?

    • Create new Account with contact

      Hi I can create a new Account and, as part of that process, add a primary contact (First name, last name) and Email. But THIS contact does NOT appear in Contacts. How can I make sure the Contact added when creating an Account is also listed as a Contact?

    • Custom Fonts in Zoho CRM Template Builder

      Hi, I am currently creating a new template for our quotes using the Zoho CRM template builder. However, I noticed that there is no option to add custom fonts to the template builder. It would greatly enhance the flexibility and branding capabilities if

    • Python - code studio

      Hi, I see the code studio is "coming soon". We have some files that will require some more complex transformation, is this feature far off? It appears to have been released in Zoho Analytics already

    • Sync desktop folders instantly with WorkDrive TrueSync (Beta)

      Keeping your important files backed up and accessible has never been easier! With WorkDrive desktop app (TrueSync), you can now automatically sync specific desktop folders to WorkDrive Web, ensuring seamless, real-time updates across devices. Important:

    • How To Insert Data into Zoho CRM Organization

      Hi Team I have this organization - https://crm.zoho.com/crm/org83259xxxx/tab/Leads I want to insert data into this Leads module, what is the correct endpoint for doing so ? Also I have using ZohoCRM.modules.ALL scope and generated necessary tokens.

    • Where can I get Equation Editor por Zoho Writer?

      I need to use Math Formulas in my document. Thank you.

    • How can I get base64 string from filecontent in widget

      Hi, I have a react js widget which has the signature pad. Now, I am saving the signature in signature field in zoho creator form. If I open the edit report record in widget then I want to display the Signature back in signature field. I am using readFile

    • Creator roadmap for the rest of 2022

      Hi everyone, Hope you're all good! Thanks for continuing to make this community engaging and informative. Today we'd like to share with you our plans for the near future of Creator. We always strive to strike a good balance of features and enhancements

    • Filtering repport for portal users

      Salut, I have a weird problem that I just cannot figure out : When I enter information as administrator on behalf of a "supplier" portal user (in his "inventory" in a shared inventory system), I can see it, "customer" portal users can see it, but the

    • Zoho Inventory. Preventing Negative Stock in Sales Orders – Best Practices?

      Dear Zoho Inventory Community, We’re a small business using Zoho Inventory with a team of sales managers. Unfortunately, some employees occasionally overlook stock levels during order processing, leading to negative inventory issues. Is there a way to

    • Zoho One - Syncing Merchants and Vendors Between Zoho Expense and Zoho Books

      Hi, I'm exploring the features of Zoho One under the trial subscription and have encountered an issue with syncing Merchant information between Zoho Expense and Zoho Books. While utilizing Zoho Expense to capture receipts, I noticed that when I submit

    • Is Zoho Sheet available for Linux ?

      Is Zoho Sheet available for Linux ?

    • Zoho Sheet for Desktop

      Does Zoho plans to develop a Desktop version of Sheet that installs on the computer like was done with Writer?

    • BUTTONS SHOWN AS AN ICON ON A REPORT

      Hi Is there any way to create an action button but show it as an icon on a report please? As per the attached example? So if the user clicks the icon, it triggers an action?

    • Dropshipping Address - Does Not Show on Invoice Correctly

      When a dropshipping address is used for a customer, the correct ship-to address does not seem to show on the Invoice. It shows correctly on the Sales Order, Shipment Order, and Package, just not the Invoice. This is a problem, because the company being

    • RFQ MODEL

      A Request for quotation model is used for Purchase Inquiries to multiple vendors. The Item is Created and then selected to send it to various vendors , once the Prices are received , a comparative chart is made for the user. this will help Zoho books

    • Will zoho thrive be integrated with Zoho Books?

      title

    • Product Updates in Zoho Workplace applications | August 2025

      Hello Workplace Community, Let’s take a look at the new features and enhancements that went live across all Workplace applications this August. Zoho Mail Delegate Email Alias Now you can let other users send emails on your behalf—not just from your primary

    • Notebook audio recordings disappearing

      I have recently been experiencing issues where some of my attached audio recordings are disappearing. I am referring specifically to ones made within a Note card in Notebook on mobile, made by pressing the "+" button and choosing "Record audio" (or similar),

    • Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked

      Hi, I sent few emails and got this: Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked And now I have few days since I cant send any email. Is there something wrong I did? Also can someone fix this please

    • Want to use Zoho Books in Switzerland. CHF support planned?

      Hi, We're a Swiss company using other Zoho suite software and I discovered Zoho Books and other accounting SaaS when looking for an accounting tool. Do you intend to cover Switzerland and CHF based accounting anytime soon? Roy

    • Zoho sheet desktop version

      Hi Zoho team Where can I access desktop version of zoho sheets? It is important as web version is slow and requires one to be online all the time to do even basic work. If it is available, please guide me to the same.

    • Weekly Tips : Teamwork made easy with Multiple Assignees

      Let's say you are working on a big project where different parts of a single task need attention from several people at the same time—like reviewing a proposal that requires input from sales, legal, and finance teams. Instead of sending separate reminders

    • Best way to share/download presentation files in Zoho without losing formatting?

      Hello Zoho Community, I often work with PPT/PDF files in Zoho Docs and share them with colleagues. While PDFs usually give a direct download option, I’ve noticed that PPT/PPTX files sometimes only open in the viewer without a clear download link. Is there

    • Next Page