Help Center
How to Generate SQL Query with Open AI: Displaying Users' Birthdays

How to Generate SQL Query with Open AI: Displaying Users' Birthdays

Discover how to generate the SQL query using the power of OpenAI to display users with birthdays this month on the current day and after. Use prompt examples to generate the SQL query.

Before you start, check the tutorial requirements:

  • Elementor (Free version) or Block editor (Gutenberg)

  • JetEngine plugin installed and activated

Open AI is a platform that provides artificial intelligence-based models that are intended to solve different problems. One of the key concepts is prompts – that’s how you build your request to generate the solution.

Now, you can use the Open AI with the SQL Query by the JetEngine plugin. With the text request (prompt), you can generate the query to display the data from the database table. For example, you can get users who published posts in the last two weeks, select product categories with products in stock, and display posts published this week.

This tutorial will show how to generate the SQL query with AI to display users with birthdays this month on the current day and after.

Create the Date Meta Field for Users

Guided by the How to Apply Meta Boxes to Users tutorial, create the Meta Box and attach the Date meta field to users. 

attach date meta field to users using meta box

You can add several meta fields of different types, for example, for users’ names, pictures, and additional information.

meta fields for users

Add several users and fill in the Date meta field for each of them.

add new users

Create the SQL Query

Proceed to the JetEngine > Query Builder tab and hit the “Add New” button.

Give the query a Name, and pick the “SQL/AI Query” Query Type.

SQL/AI query type

Enable the Advanced/AI mode toggle, and you’ll see that the settings are different than if you decided to write SQL query manually.

To use AI, press the magic stick icon, and the pop-up for the SQL query generation will appear.

ai mode for SQL query
Warning

Currently, you can make 30 requests per month with the Lifetime subscription plan and 5 requests per month with other subscriptions.

In the text area, you can describe what data you want to display with the query. 

Also, you can find tips for the successful prompt (text request with the query description) writing and prompt examples.

generate query with AI

Prompt examples are clickable, so you can select one and use it for the SQL query. I selected “Get users who have birthday on current month. Birthday is stored in ‘birth_date’ meta field. Return only future birthdays and all data from the users table.” 

example of the text request with the query description

Then, I pushed the “Generate query” button.

generate query button

After that, you will see the generated query in the text area.

generated query with AI
Warning

Generated query with AI might not be accurate, so we recommend reviewing it before using it.

For example, in the

WHERE m.meta_key = 'birth_date' 

line, the ‘birth_date’ value is default, so you need to replace it with the slug of the Date meta field you created earlier. I replaced it with the ‘_employee-birthday’ slug.

replace the meta key in the generated query

After reviewing, click the “Use this query” button.

The generated query will appear in the SQL Query text area, where you can edit it as well.

Also, pay attention to the Cast result to instance of object option. The SQL query returns items as the default PHP objects, and with this option, you can convert default objects into the needed ones (posts, users, terms, etc.). This helps avoid errors when working with widgets customized to particular objects (posts, users).

In this case, you need to select the “User” option.

cast result to instance of object

After you finish, press the “Add/Update Query” button.

Create a Listing Item

Move to the JetEngine > Listings tab and click the “Add New” button. 

In the Setup Listing Item pop-up, you can select the “Users” or “Query Builder” Source. If you choose the “Query Builder” Source, you need to pick the needed Query as well. Enter the Listing item name and choose the “Elementor” or “Blocks (Gutenberg)” Listing view. Then, click the “Create Listing Item” button.

setup listing item with the query builder source

I used the Dynamic Field widget to display the users’ information. To learn more, proceed to the Dynamic Field Overview.

If you want to display the data from the default WordPress fields for users, select the “Post/Term/User/Object Data” Source.

If you want to display the data from the JetEngine custom fields from the meta box, pick the “Meta Data” Source.

To display images, enable the Filter field output option and choose the “Get image by ID” Callback. Navigate to the Filter Field Output Overview to learn how to display different data with different callbacks. 

get image by ID callback

If you want to display only the day and month instead of the full date, enable the Filter field output option, pick the “Format date” Callback, and enter “F j” instead of “F j, Y.”

format date callback

After you done customizing, press the “Update” button.

Add the Listing Grid to the Page

Move to the Pages tab and hit the “Add New” button or click to edit the created page.

Place the Listing Grid widget or block to the page and select the recently created Listing in the General settings.

If you created the Listing Item with the “Query Builder” Source, the Listing Grid would show the query results immediately.

Today is 25th May, so the query shows only users whose birthday is in the current month on the current day and after, as that was set in the prompt example for the SQL query generation.

listing grid on the page

If you created the Listing Item with the “Users” Source, open the Custom Query tab, enable the Use Custom Query toggle, and choose the needed Custom Query

That’s all. Now you know how to generate the SQL query with the Open AI to display users with birthdays this month on the current day and after.

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.