How to Display WooCommerce Product Categories with Products in Stock Using Open AI Query

Create new custom query

Firstly, make sure that the main license key is activated in WordPress Dashboard > Crocoblock > License.

Then, proceed to WordPress Dashboard > JetEngine > Query Builder > Add New to create a new query.  

Select “SQL/AI Query” as the Query Type. The generating tool will be located in the settings of the “SQL/AI Query”.

Give a Name to your query. Description and Query ID fields are optional.

Activate the Advanced/AI mode

The first toggle in the query settings prompts to activate the Advanced/AI mode. Toggle it on.

Now that the Advanced mode is activated, new settings appear. In the SQL Query area, you can write your own SQL Query. To open the AI generator tool, click the “Magic Wand” button in the top right corner of the SQL Query field area.

Write prompt

The popup includes the field for entering the prompt, useful tips on writing a good prompt, and prompt examples.

You can type in the needed request in the Describe your query field. The response will be received through the API, and the SQL query will be parsed from it and inserted.

Prompt examples are clickable; when clicking on one, the prompt will be entered into the field to generate a further query based on it. Mind that prompts from the examples still count in the general limit of requests to AI, so use the generator only when you are sure of your prompt.

We will use one of the ready examples to generate the query: “WooCoomerce. Select product categories with products in stock. Product stock status is stored in ‘_stock_status’ meta field. Return only unique terms and all data from terms table.”

After entering the prompt and clicking on the “Generate query” button, the query generation process takes place. The process requires from several seconds to a minute to finish. 

If successful, the generated query will be added to the same field, replacing the initial request, where it can be corrected and further applied. If necessary, generate another query using a new prompt by clicking the “Generate new query” button. Remember that each time a new query is generated counts as one request.

Try more complex cases with dynamic data

Now, let’s try to adjust the prompt. 

Assuming we need to retrieve only first-level product categories (regardless of whether they have child categories), we would write this prompt: “WooCommerce. Select only first-level product categories. Return only unique terms and all data from terms table”.

In our example, we have five product categories, only three of them being first-level categories.

In the results of the query, we see these three categories.

This custom query is ready for use as it is — it retrieves unique product categories where the parent category equals 0 (remember that the ‘term_taxonomy’ table has a column named ‘parent’ that stores the ID of the parent term; if the value of the ‘parent’ column equals 0, it means, the category is the first-level category because it has no parents). 

Now, let’s try something more complex. The next goal is to retrieve only second-level product categories that belong to the current product. 

We have such a tree of product categories: Clothing > Women’s Collections > Spring Collection.

For this product, the retrieved category should be “Women’s Collections”. 

Before writing the prompt, we should break our task into smaller parts and elaborate on each part. Remember that the best practice is to write the prompt in a way you would structure the SQL query itself. Keep the description concise — prompts have a limit of 400 characters. The lengthier the prompt is, the harder it is for AI to understand it. 

So, the parts we should consider are: 

1. The AI should look in WooCommerce database tables;
2. The AI should select only second-level product categories;
3. The product categories should belong to the current product. We know that the ID of the current product can be retrieved dynamically with the %current_id% macro (check the Macros Guide for more information). It is important to mention it to the AI;
4. The parent product category of the retrieved product category should be a parent category itself. Therefore, the parent category of the retrieved category should not have parents. This way, we select only second-level terms and not all terms that count as child terms (e.g., the ‘Spring Collection’ product category should not be included in the results);
5. Terms should be unique;
6. All data from the terms table should be retrieved.

Having considered the points above, we can write the following prompt:

‘WooCoomerce. Select only second-level product categories of the current product. The parent product category of the returned product categories should not have a parent category. The ID of the current product is retrieved with %current_id% macro. Return only unique terms and all data from terms table.’

The result is exactly what we need.

Nevertheless, there is an area for improvement. To optimize the query, we can remove the line that says ‘AND tt.parent != 0;’.

Review and adjust the query

Once you are satisfied with the result, click the “Use this query” button to paste the query into the SQL Query field. We will stick with the first generated query.

You can activate the Preview results toggle to see if the results match your expectations. The query results should display the data from the product categories that have products in stock.

In our example, the query returns four properties — ‘term_id,’ ‘name,’ ‘slug,’ and ‘term_group.’ These are the only properties available for choosing in the Dynamic Field widget/block when you move on to designing the Listing Template. It means this is the only dynamic data you can display in the listing. Other category fields that are default to WooCommerce cannot be used (e.g., category thumbnail). Also, if you have meta fields attached to product categories, they cannot be retrieved. 

In some cases, the given properties are enough to style the Listing Template. However, often it is important to have access to all the fields related to the object (such as the thumbnail and meta fields of the product category). For this, scroll the settings of the custom query down to the Cast result to instance of object setting.

Select the “Taxonomy Term” option. Now, the objects in the query results are converted to taxonomy terms, and you are not limited to the four properties returned by the SQL Query anymore.

Press the “Add Query” button to save the query.

There are two ways to display the query results — either by creating a Listing Template for Query Builder and using properties returned by SQL query to build the listing; or by applying the query on a Listing Grid with a ready Listing Template for product categories.

Create a new listing template for SQL query

Navigate to WordPress Dashboard > JetEngine > Listings > Add New to create a new Listing Template.

Select the “Query Builder” option as the Listing source. Next, select the previously created SQL Query. Give a name to the listing template. Finally, select the Listing view. We will use “Elementor” in our example. 

Push the “Create Listing Item” button to go to the editor of the template.

Fill the listing template with content

Now, it is time to design the listing template. Later, it will define the layout of the Listing Grid.

Use the Dynamic Field to display data from the product category — for example, product category name. 

To display the product category name, select the “Post/Term/User/Object Data” option as the Source of the Dynamic Field; then, select “Term name” in the Object Field dropdown.

The same settings are applied in Gutenberg.

To display the product category thumbnail, place the Dynamic Image in the editing area. In the Custom meta field/repeater key field, insert the ‘thumbnail_id’ field name:

Once you finish styling the listing, save the changes.

Display the listing using the Listing Grid widget 

Move to the page or template where the product categories should be displayed and open it in the editor. 

Place the Listing Grid widget/block on the page. Select the Listing template created previously.

Note that you do not need to apply the query in the Custom Query tab of Listing Grid settings.