COQL Query Builder

A lightweight query builder for Zoho CRM that abstracts the COQL queries and provides a fluent API for building them.

This library is an independent open-source project and is not affiliated with or endorsed by Zoho Corporation. ‘Zoho’ is trademarks of Zoho Corporation, used here only to describe compatibility.

Installation

npm install @doralhw-org/coql-query-builder

Usage

You'll need to create a object of the query builder and pass it a function that can fetch the data with the provided query. This function must return the data in the same format as the Zoho CRM API response.

import { CoqlQueryBuilder } from "@doralhw-org/coql-query-builder";

const coqlQueryBuilder = new CoqlQueryBuilder({
  queryFunction: queryCrm,
});

Fetching data

After creating the query builder object you can use the select method to fetch the data. The query passed will then be turned to one or more COQL queries and executed. The data will then be merged together and returned as an array of records.

const records = await coqlQueryBuilder.select({
  columns: [
    "id",
    "Name",
    "Age",
    "Role",
    "Email",
    { column: "Address_Line_1", alias: "Address" },
  ],
  from: "Contacts",
  where: [{ equals: [{ column: "id" }, { value: "123" }] }],
});

This merge is performed based on the id column. If the id column is not added as a column to the query, it will be added to the queries generated. If the id column has a different name, or you want to group based on a different identifier column, you may alias the column to id .

Alternatively, you can use the buildQuery method to build the query and then execute it on your own. The method will return one or more COQL queries depending on the workarounds required to obtain the data based on the limitations of the Zoho CRM API.

const queries = coqlQueryBuilder.buildQuery({
  columns: [
    "id",
    "Name",
    "Age",
    "Role",
    "Email",
    { column: "Address_Line_1", alias: "Address" },
  ],
  from: "Contacts",
  where: [{ equals: [{ column: "id" }, { value: "123" }] }],
});

Documentation

For comprehensive documentation including detailed usage patterns, limitations, and workarounds for each query clause, see the Query Builder Documentation.

Where Conditions

The available where conditions are:

• equals
• greaterThan
• lessThan
• greaterThanOrEquals
• lessThanOrEquals
• between
• in
• like

These can be negated by wrapping the condition object within a not attribute and combined with the and and or operators. The where clause array will join the conditions with the and operator by default.

where: [
  { not: { equals: [{ column: "id" }, { value: "123" }] } },
  {
    and: [
      { greaterThan: [{ column: "Age" }, { value: "30" }] },
      { lessThan: [{ column: "Age" }, { value: "40" }] },
    ],
  },
  {
    or: [
      { in: { column: "Name", valueSet: ["John", "Jane"] } },
      { like: { column: "Name", pattern: "John%" } },
    ],
  },
  {
    like: { column: "Email", pattern: "%@example.com" },
  },
];

For detailed information about where clause structure, limitations, and workarounds, see the Where Clause section in the full documentation.