How to Set Query Builder for REST API Items

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

Two other items in the results have ‘one’ and ‘two’ options checked as per the 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.

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.

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.

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.