{"id":40003,"date":"2023-05-29T12:12:25","date_gmt":"2023-05-29T12:12:25","guid":{"rendered":"https:\/\/crocoblock.com\/knowledge-base\/?post_type=article&p=40003"},"modified":"2023-07-28T13:49:31","modified_gmt":"2023-07-28T13:49:31","slug":"how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query","status":"publish","type":"article","link":"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/","title":{"rendered":"How to Display WooCommerce Product Categories with Products in Stock Using Open AI Query"},"content":{"rendered":"\n\"sql\n\n\n
<\/svg><\/div>
Things to know<\/div>

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 \u2014 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.<\/p>\n

Please activate or upgrade your license to use AI functionality.<\/p>\n

Also you can generate query with any public AI chat.
\n<\/p><\/div><\/div>\n\n\n\n

How to Generate the SQL Query with the Open AI<\/h2>\n\n\n\n
\n

Create new custom query<\/h3>\n\n\n\n

Firstly, make sure that the main license key is activated in WordPress Dashboard > Crocoblock > License<\/em><\/strong>.<\/em><\/p>\n\n\n\n

Then, proceed to WordPress Dashboard > JetEngine > Query Builder > Add New <\/em><\/strong>to create a new query.  <\/p>\n\n\n\n

Select \u201cSQL\/AI Query\u201d as the Query Type<\/strong>. The generating tool will be located in the settings of the \u201cSQL\/AI Query\u201d.<\/p>\n\n\n\n\"creating\n\n\n\n

Give a Name <\/strong>to your query. Description<\/strong> and Query ID <\/strong>fields are optional.<\/p>\n\n\n\n

Activate the Advanced\/AI mode<\/h3>\n\n\n\n

The first toggle in the query settings prompts to activate the Advanced\/AI mode<\/strong>. Toggle it on.<\/p>\n\n\n\n\"activating\n\n\n\n

Now that the Advanced<\/strong> mode <\/strong>is activated, new settings appear. In the SQL Query <\/strong>area, you can write your own SQL Query. To open the AI generator tool, click the \u201cMagic Wand<\/strong>\u201d <\/strong>button in the top right corner of the SQL Query <\/strong>field area.<\/p>\n\n\n\n\"overview\n\n\n\n

Write prompt<\/h3>\n\n\n\n

The popup includes the field for entering the prompt, useful tips on writing a good prompt, and prompt examples.<\/p>\n\n\n\n\"sql\n\n\n\n

You can type in the needed request in the Describe your query <\/strong>field. The response will be received through the API, and the SQL query will be parsed from it and inserted.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

We will use one of the ready examples to generate the query: \u201cWooCoomerce. 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.\u201d<\/p>\n\n\n\n\"generating\n\n\n\n

After entering the prompt and clicking on the <\/strong>\u201cGenerate query<\/strong>\u201d <\/strong>button, the query generation process takes place. The process requires from several seconds to a minute to finish. <\/p>\n\n\n\n

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 \u201cGenerate new query<\/strong>\u201d button. Remember that each time a new query is generated counts as one request.<\/p>\n\n\n\n\"sql\n\n\n

<\/svg><\/div>
Warning<\/div>

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.<\/p><\/div><\/div>\n\n\n\n

Try more complex cases with dynamic data<\/h3>\n\n\n\n

Now, let\u2019s try to adjust the prompt. <\/p>\n\n\n\n

Assuming we need to retrieve only first-level product categories (regardless of whether they have child categories), we would write this prompt: \u201cWooCommerce. Select only first-level product categories. Return only unique terms and all data from terms table\u201d.<\/p>\n\n\n\n\"select\n\n\n\n

In our example, we have five product categories, only three of them being first-level categories.<\/p>\n\n\n\n\"product\n\n\n\n

In the results of the query, we see these three categories.<\/p>\n\n\n\n\"results\n\n\n\n

This custom query is ready for use as it is \u2014 it retrieves unique product categories where the parent category equals 0 (remember that the \u2018term_taxonomy\u2019 table has a column named \u2018parent\u2019 that stores the ID of the parent term; if the value of the \u2018parent\u2019 column equals 0, it means, the category is the first-level category because it has no parents). <\/p>\n\n\n\n

Now, let\u2019s try something more complex. The next goal is to retrieve only second-level product categories that belong to the current product. <\/p>\n\n\n\n

We have such a tree of product categories: Clothing > Women\u2019s Collections > Spring Collection.<\/p>\n\n\n\n\"tree\n\n\n\n

For this product, the retrieved category should be \u201cWomen\u2019s Collections\u201d. <\/p>\n\n\n\n

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 \u2014 prompts have a limit of 400 characters. The lengthier the prompt is, the harder it is for AI to understand it. <\/p>\n\n\n\n

So, the parts we should consider are: <\/p>\n\n\n\n

    \n
  1. The AI should look in WooCommerce <\/em>database tables;<\/li>\n\n\n\n
  2. The AI should select only second-level product categories;<\/li>\n\n\n\n
  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<\/a> for more information). It is important to mention it to the AI;<\/li>\n\n\n\n
  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 \u2018Spring Collection\u2019 product category should not be included in the results);<\/li>\n\n\n\n
  5. Terms should be unique;<\/li>\n\n\n\n
  6. All data from the terms table should be retrieved.<\/li>\n<\/ol>\n\n\n\n

    Having considered the points above, we can write the following prompt:<\/p>\n\n\n\n

    \u2018WooCoomerce. 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.\u2019<\/p>\n\n\n\n

    The result is exactly what we need.<\/p>\n\n\n\n\"sql\n\n\n\n

    Nevertheless, there is an area for improvement. To optimize the query, we can remove the line that says \u2018AND tt.parent != 0;\u2019.<\/p>\n\n\n\n\"adjusting\n\n\n\n

    Review and adjust the query<\/h3>\n\n\n\n

    Once you are satisfied with the result, click the \u201cUse this query<\/strong>\u201d <\/strong>button to paste the query into the SQL Query <\/strong>field. We will stick with the first generated query.<\/p>\n\n\n\n\"using\n\n\n\n

    You can activate the Preview results <\/strong>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.<\/p>\n\n\n\n\"activating\n\n\n\n

    In our example, the query returns four properties \u2014 \u2018term_id,\u2019 \u2018name,\u2019 \u2018slug,\u2019 and \u2018term_group.\u2019 These are the only properties available for choosing in the Dynamic Field<\/strong><\/a> <\/strong>widget\/block when you move on to designing the Listing Template<\/strong><\/a>. It means this is the only dynamic data you can display in the listing. Other category fields that are default to WooCommerce <\/em>cannot be used (e.g., category thumbnail). Also, if you have meta fields attached to product categories, they cannot be retrieved. <\/p>\n\n\n\n

    In some cases, the given properties are enough to style the Listing Template<\/strong>. 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<\/strong> setting.<\/p>\n\n\n\n\"using\n\n\n\n

    Select the \u201cTaxonomy Term\u201d 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 <\/strong>anymore.<\/p>\n\n\n\n

    Press the \u201cAdd Query<\/strong>\u201d <\/strong>button to save the query.<\/p>\n<\/div><\/div>\n\n\n\n

    How to Display the Results of the SQL Query Using a Listing Template<\/h2>\n\n\n\n
    \n

    There are two ways to display the query results \u2014 either by creating a Listing Template <\/strong>for Query Builder <\/strong>and using properties returned by SQL query <\/strong>to build the listing; or by applying the query on a Listing Grid<\/strong><\/a> with a ready Listing Template<\/strong> for product categories.<\/p>\n\n\n

    <\/svg><\/div>
    Warning<\/div>

    It is important to note that it is required to create a new Listing Template<\/strong> with the \u201cQuery Builder\u201d source if you didn\u2019t use the Cast results to instance of object<\/strong> setting.<\/p><\/div><\/div>\n\n\n\n

    Create a new listing template for SQL query<\/h3>\n\n\n\n

    Navigate to WordPress Dashboard > JetEngine > Listings > Add New <\/em><\/strong>to create a new Listing Template<\/strong>.<\/p>\n\n\n\n\"creating\n\n\n\n

    Select the \u201cQuery Builder\u201d option as the Listing source<\/strong>. Next, select the previously created SQL Query<\/strong>. Give a name to the listing template. Finally, select the Listing view<\/strong>. We will use \u201cElementor\u201d in our example. <\/p>\n\n\n\n

    Push the \u201cCreate Listing Item<\/strong>\u201d <\/strong>button to go to the editor of the template.<\/p>\n\n\n\n

    Fill the listing template with content<\/h3>\n\n\n\n

    Now, it is time to design the listing template. Later, it will define the layout of the Listing Grid<\/strong><\/a>.<\/p>\n\n\n\n

    Use the Dynamic Field<\/strong><\/a> to display data from the product category \u2014 for example, product category name. <\/p>\n\n\n\n

    To display the product category name, select the \u201cPost\/Term\/User\/Object Data\u201d option as the Source <\/strong>of the Dynamic Field<\/strong>; then, select \u201cTerm name\u201d in the Object Field <\/strong>dropdown.<\/p>\n\n\n\n\"using\n\n\n\n

    The same settings are applied in Gutenberg.<\/p>\n\n\n\n\"using\n\n\n\n

    To display the product category thumbnail, place the Dynamic Image <\/strong>in the editing area. In the Custom meta field\/repeater key<\/strong> field, insert the \u2018thumbnail_id\u2019 field name:<\/p>\n\n\n\n\"using\n\n\n\n

    Once you finish styling the listing, save the changes.<\/p>\n\n\n\n

    Display the listing using the Listing Grid widget <\/h3>\n\n\n\n

    Move to the page or template where the product categories should be displayed and open it in the editor. <\/p>\n\n\n\n

    Place the Listing Grid <\/strong>widget\/block on the page. Select the Listing <\/strong>template created previously.<\/p>\n\n\n\n\"displaying\n\n\n\n

    Note that you do not need to apply the query in the Custom Query <\/strong>tab of Listing Grid <\/strong>settings.<\/p>\n<\/div><\/div>\n\n\n\n

    That\u2019s it. Now, your listing is using a custom query generated by AI to display product categories with products in stock.<\/p>\n","protected":false},"excerpt":{"rendered":"

    Learn how to generate SQL Query in JetEngine’s Query Builder using the AI Query feature to showcase WooCommerce product categories with products in stock.<\/p>\n","protected":false},"author":9,"featured_media":0,"template":"","format":"standard","builder-category":[],"article-category":[405],"yoast_head":"\nGenerate SQL Query for WooCommerce Products in Stock with OpenAI | Crocoblock<\/title>\n<meta name=\"description\" content=\"Learn how to generate SQL Query in JetEngine's Query Builder using the AI Query feature to showcase WooCommerce product categories with products in stock.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Generate SQL Query for WooCommerce Products in Stock with OpenAI | Crocoblock\" \/>\n<meta property=\"og:description\" content=\"Learn how to generate SQL Query in JetEngine's Query Builder using the AI Query feature to showcase WooCommerce product categories with products in stock.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/\" \/>\n<meta property=\"og:site_name\" content=\"Help Center\" \/>\n<meta property=\"article:modified_time\" content=\"2023-07-28T13:49:31+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/crocoblock.com\/knowledge-base\/wp-content\/uploads\/2023\/05\/sql-query-generated-by-ai-in-query-builder-of-jetengine_2x.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"12 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/\",\"url\":\"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/\",\"name\":\"Generate SQL Query for WooCommerce Products in Stock with OpenAI | Crocoblock\",\"isPartOf\":{\"@id\":\"https:\/\/crocoblock.com\/knowledge-base\/#website\"},\"datePublished\":\"2023-05-29T12:12:25+00:00\",\"dateModified\":\"2023-07-28T13:49:31+00:00\",\"description\":\"Learn how to generate SQL Query in JetEngine's Query Builder using the AI Query feature to showcase WooCommerce product categories with products in stock.\",\"breadcrumb\":{\"@id\":\"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/crocoblock.com\/knowledge-base\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Articles\",\"item\":\"https:\/\/crocoblock.com\/knowledge-base\/articles\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"How to Display WooCommerce Product Categories with Products in Stock Using Open AI Query\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/crocoblock.com\/knowledge-base\/#website\",\"url\":\"https:\/\/crocoblock.com\/knowledge-base\/\",\"name\":\"Help Center\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/crocoblock.com\/knowledge-base\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/crocoblock.com\/knowledge-base\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/crocoblock.com\/knowledge-base\/#organization\",\"name\":\"Help Center\",\"url\":\"https:\/\/crocoblock.com\/knowledge-base\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/crocoblock.com\/knowledge-base\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/crocoblock.com\/knowledge-base\/wp-content\/uploads\/2025\/04\/invert-crocoblock-logo.svg\",\"contentUrl\":\"https:\/\/crocoblock.com\/knowledge-base\/wp-content\/uploads\/2025\/04\/invert-crocoblock-logo.svg\",\"caption\":\"Help Center\"},\"image\":{\"@id\":\"https:\/\/crocoblock.com\/knowledge-base\/#\/schema\/logo\/image\/\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Generate SQL Query for WooCommerce Products in Stock with OpenAI | Crocoblock","description":"Learn how to generate SQL Query in JetEngine's Query Builder using the AI Query feature to showcase WooCommerce product categories with products in stock.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/","og_locale":"en_US","og_type":"article","og_title":"Generate SQL Query for WooCommerce Products in Stock with OpenAI | Crocoblock","og_description":"Learn how to generate SQL Query in JetEngine's Query Builder using the AI Query feature to showcase WooCommerce product categories with products in stock.","og_url":"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/","og_site_name":"Help Center","article_modified_time":"2023-07-28T13:49:31+00:00","og_image":[{"url":"https:\/\/crocoblock.com\/knowledge-base\/wp-content\/uploads\/2023\/05\/sql-query-generated-by-ai-in-query-builder-of-jetengine_2x.png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"12 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/","url":"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/","name":"Generate SQL Query for WooCommerce Products in Stock with OpenAI | Crocoblock","isPartOf":{"@id":"https:\/\/crocoblock.com\/knowledge-base\/#website"},"datePublished":"2023-05-29T12:12:25+00:00","dateModified":"2023-07-28T13:49:31+00:00","description":"Learn how to generate SQL Query in JetEngine's Query Builder using the AI Query feature to showcase WooCommerce product categories with products in stock.","breadcrumb":{"@id":"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/crocoblock.com\/knowledge-base\/jetengine\/how-to-display-woocommerce-categories-with-products-in-stock-jetengine-ai-query\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/crocoblock.com\/knowledge-base\/"},{"@type":"ListItem","position":2,"name":"Articles","item":"https:\/\/crocoblock.com\/knowledge-base\/articles\/"},{"@type":"ListItem","position":3,"name":"How to Display WooCommerce Product Categories with Products in Stock Using Open AI Query"}]},{"@type":"WebSite","@id":"https:\/\/crocoblock.com\/knowledge-base\/#website","url":"https:\/\/crocoblock.com\/knowledge-base\/","name":"Help Center","description":"","publisher":{"@id":"https:\/\/crocoblock.com\/knowledge-base\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/crocoblock.com\/knowledge-base\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/crocoblock.com\/knowledge-base\/#organization","name":"Help Center","url":"https:\/\/crocoblock.com\/knowledge-base\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/crocoblock.com\/knowledge-base\/#\/schema\/logo\/image\/","url":"https:\/\/crocoblock.com\/knowledge-base\/wp-content\/uploads\/2025\/04\/invert-crocoblock-logo.svg","contentUrl":"https:\/\/crocoblock.com\/knowledge-base\/wp-content\/uploads\/2025\/04\/invert-crocoblock-logo.svg","caption":"Help Center"},"image":{"@id":"https:\/\/crocoblock.com\/knowledge-base\/#\/schema\/logo\/image\/"}}]}},"_links":{"self":[{"href":"https:\/\/crocoblock.com\/knowledge-base\/wp-json\/wp\/v2\/article\/40003"}],"collection":[{"href":"https:\/\/crocoblock.com\/knowledge-base\/wp-json\/wp\/v2\/article"}],"about":[{"href":"https:\/\/crocoblock.com\/knowledge-base\/wp-json\/wp\/v2\/types\/article"}],"author":[{"embeddable":true,"href":"https:\/\/crocoblock.com\/knowledge-base\/wp-json\/wp\/v2\/users\/9"}],"wp:attachment":[{"href":"https:\/\/crocoblock.com\/knowledge-base\/wp-json\/wp\/v2\/media?parent=40003"}],"wp:term":[{"taxonomy":"builder-category","embeddable":true,"href":"https:\/\/crocoblock.com\/knowledge-base\/wp-json\/wp\/v2\/builder-category?post=40003"},{"taxonomy":"article-category","embeddable":true,"href":"https:\/\/crocoblock.com\/knowledge-base\/wp-json\/wp\/v2\/article-category?post=40003"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}