Help Center
How to Set Query Builder for REST API Items

How to Set Query Builder for REST API Items

This tutorial explains how to query a Custom Content Type endpoint by different field types using JetEngine’s Query Builder.

Before you start, check the tutorial requirements:

Data Types

Let us start with understanding which data types can be queried with which operators in the Custom Query of REST API type.

In the JetEngine plugin folder, you can find a file that lists the data types and operators by following this path:

“\jet-engine\includes\modules\custom-content-types\inc\query-dialog.php”.

Understanding the data types helps write the appropriate data type and operator in the Query Clause, so the values are converted if needed and when making a query to the database, cast to a specified type.

These are the data types:

  • with no conversion:
    • ‘integer’ — a non-decimal number;
    • ‘float’ —  a number with a decimal point;
    • ‘char’ — string values.
  • with conversion:
    • ‘timestamp’ — number of seconds between a particular date and the Unix Epoch, which starts on January 1st, 1970 at UTC; the values you enter will be converted to UNIX timestamp. For example, ‘13.07.2023’ will be converted to 1689199200 before making a REST API call, ‘today‘ will be converted to today’s date in timestamp;
    • ‘date’ — is a date in MySQL DATETIME format; the date will be converted to it; for example, ‘13.07.2023’ will be converted to ‘13.07.2023 00:00:00’.

Here are also the operators. They define a condition by which the entered value in the query clause is compared with the values in the items:

  • Equal;
  • Not equal;
  • Greater than;
  • Greater or equal;
  • Less than;
  • Equal or less;
  • Like;
  • Not like;
  • In;
  • Not in;
  • Between;
  • Not between.

Connect to CCT Endpoint

In the WordPress Dashboard > JetEngine > REST API Endpoints tab, build a connection to an endpoint with the CCT items from another website.

add new query button

Query Clause Examples

Go to WordPress Dashboard > JetEngine > Query Builder and create a Custom Query by pressing the “Add New” button.

Select “REST API Query” as a Query Type, and pick an endpoint previously connected in JetEngine’s REST API in the From Endpoint field. 

We recommend toggling on Preview results at the top of the page to see the query results instantly on the right side.

rest api query type

Add a new item to the Query and ‘_filters’ as a Key.

We add this exact key because in the folder of the JetEngine plugin (in the “\jet-engine\includes\modules\custom-content-types\inc\rest-api\public-controller.php” file), you can find a ‘_filters’ parameter which allows querying the CCT items.

rest api query filters key

Integer

Check the first example with the ‘f2’ field that stores a value of “integer” data type.

In this case, the value for the query clause should be written in the following format:

[{"field":"f2","operator":"=","value":"12","type":"INTEGER"}]

It means we want to pull the items with a value of ‘12’ in the ‘f2’ field. We also specify that the value is an integer. With this query clause, we instantly get one item in the Preview Results window which matches our request.

integer value in query

Floating number

The second example is a query clause based on a “floating number” value.

We wrote the following value:

 [{"field":"num2","operator":">","value":"67.50","type":"FLOATING"}] 

As a result, we got one CCT item with a value greater than 67.50.

floating number value in query

String

The following example is a query clause based on the value of the CCT item field, which stores a “string” value.

It is a value for the query clause:

[{"field":"cct_status","operator":"=","value":"publish","type":"CHAR"}]

With this request, we got three CCT items with the ‘publish’ status.

string value in query

Checkbox field

Let us proceed with the example of querying by the checkbox field. The checkbox stores items in a serialized array in the database, so we will use the CHAR type.

The checkbox in our example field has two options — ‘one’ and ‘two’. We will check how the query is to be written in different cases.

To show the items that have at least option ‘one’ checked, we use this value in the query clause:

[{"field":"check1","operator":"LIKE","value":"one","type":"CHAR"}]

In this case, we receive items with option ‘one’ checked and other options.

checkbox value in query with one option checked

To show the items where only one specific item is checked, this value in the query clause is needed:

 [{"field":"check1","operator":"REGEXP","value":"a:1:.*one","type":"CHAR"}]

The result displays the item with only the option ‘one’ checked. Other items with options ‘one’ and ‘two’ checked are ignored.

specific item checked query

Let us also see how we can show the CCT items that have only both specific options checked simultaneously, not just one. In other words, the items have to be queried with a relational operator ‘AND’. We came up with the following value for the query clause:

[{"field":"check1","operator":"LIKE","value":"one","type":"CHAR"},
{"field":"check1","operator":"LIKE","value":"two","type":"CHAR"}]

Now the result displays the items that have only both options checked at the same time.

both options checked query

Another case for querying by checkbox field is showing items with at least one value checked from the list. It means a relational operator ‘OR’ is used.

[
{ "relation":"OR",
"0":{"field":"f2","operator":"=","value":"12","type":"CHAR"},
"1":{"field":"f1","operator":"=","value":"1","type":"CHAR"}
}
]

The results will show items with option ‘one’ checked, just option ‘two’ checked, or both simultaneously.

relational operator or

And last but not least case for querying by checkbox field is creating a query clause with two relational operators — ‘OR’ and ‘AND’. It means the query has to present the items with option ‘one’ checked or options ‘one’ and ‘two’ checked simultaneously.

You can see the query clause value for such a case below. You can also compare values from other fields, not just from one, to create a more advanced query.

[
  {
    "relation":"OR",
    "0" : {
            "relation" : "AND",
            "0" : {
                    "field" : "check1",
                    "operator" : "LIKE",
                    "value" : "one",
                    "type" : "CHAR"
                   },
            "1" : {
                    "field" : "check1",
                    "operator" : "LIKE",
                    "value" : "two",
                    "type"  : "CHAR"}
           },
    "1" : {
            "field" : "check1",
            "operator" : "LIKE",
            "value" : "one",
            "type" : "CHAR"
           }
  }
]

Here is an item in the results with only option ‘one’ checked.

only one option checked

Two other items in the results have ‘one’ and ‘two’ options checked as per the query.

two options checked as per query

Lastly, let us query the CCT items by the fields that store dates in date or timestamp formats.

Date

We have a ‘cct_created’ field that stores a date in a date format.

date query

Therefore, we can use the following expression to query by this field:

[{"field":"cct_created","operator":"BETWEEN","value":"2023-01-01,2023-04-22","type":"DATE"}]

As a result, we have an item that has been created between the entered dates on 21st April 2023.

date query result

Timestamp

A frequent request is to show items with a date greater or equal to ‘today’ based on the timestamp.

As you can see, the ‘date1’ has a date saved as a timestamp, so we need to use the ’TIMESTAMP’ type in the query clause.

timestamp query

Here is how the query should be written in this case:

[{"field":"date1","operator":">=","value":"today","type":"TIMESTAMP"}]

In this case, we get a post with a date greater than ‘today’ because the ‘1698624000’ timestamp is ‘Mon Oct 30 2023’ in GMT.

timestamp query result

Besides the ‘today’ value, you can use other relative date/time formats

That is it. Now you know how to query JetEngine CCT items pulled via REST API by different data types using different operators and conditions on your WordPress website.

Was this article helpful?
YesNo

Need help?

Ask Facebook community

Get answers from 22K Crocoblock Community experts from all over the world.

Start a live chat

Ask question to support agent and share its details via chat messages.