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

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

This guide reveals step-by-step instructions on how to generate an SQL Query in the Query Builder tool of JetEngine using Open AI to showcase product categories that have products in stock.

Before you start, check the tutorial requirements:

  • WooCommerce plugin installed and activated with products and product categories created

  • JetEngine plugin at least 3.2.0 version installed and activated

sql query generated by ai in query builder of jetengine
Things to know

AI functionality is allowed only for subscription plans that include the JetEngine plugin (Lifetime, All-Inclusive, and Custom Subscriptions where JetEngine is included). The number of requests to AI is limited — 30 requests per month for Lifetime subscriptions and 5 requests per month for other subscriptions. This is the general limit for all websites that use the given license key (therefore, all websites share the same limit). Mind that the AI functionality is available only for the main key. It is not available for sub-keys.

Please activate or upgrade your license to use AI functionality.

Also you can generate query with any public AI chat.

How to Generate the SQL Query with the Open AI

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”.

creating 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.

activating advanced ai tool in the sql query settings

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.

overview of the settings of the advanced ai mode in sql query

Write prompt

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

sql query generator tool overview

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.”

generating woocommerce sql query using ai in jetengine query builder

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.

sql query generated by ai in query builder of jetengine
Warning

Please note that the generated SQL query is created by the AI system to match your description best. Remember to review the query carefully, as it might not be 100% accurate.

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”.

select only first-level product categories with ai query

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

product categories of woocommerce overview

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

results for query retrieving only first-level product 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.

tree of product categories

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.

sql query that retrieves second-level product categories of current product

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

adjusting the sql query generated by ai

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.

using generated sql query by ai in sql ai 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.

activating preview results mode in sql ai query

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.

using cast result to instance of object

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.

How to Display the Results of the SQL Query Using a Listing Template

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.

Warning

It is important to note that it is required to create a new Listing Template with the “Query Builder” source if you didn’t use the Cast results to instance of object setting.

Create a new listing template for SQL query

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

creating listing template for sql query to display woocommerce categories in stock

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.

using dynamic field widget to display information about product category of woocommerce

The same settings are applied in Gutenberg.

using dynamic field block to display information about product category of woocommerce

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:

using dynamic image to display product category thumbnail

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.

displaying product categories of woocommerce in listing grid widget

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

That’s it. Now, your listing is using a custom query generated by AI to display product categories with products in stock.

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.