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

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

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

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

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.

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.

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.

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

Then, I pushed the “Generate query” button.

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

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.

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.

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

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.

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. 

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

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

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.

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.