Help Center
How to Show the User's Posts Reviews with Average Rating Count on the Single User Profile Page

How to Show the User's Posts Reviews with Average Rating Count on the Single User Profile Page

This tutorial explains how to create an SQL query to show an average rating count submitted to the posts published by the queried user and output that count.

Before you start, check the tutorial requirements:

Create an SQL Query

Start with building an SQL query to retrieve the needed data.

Go to WordPress Dashboard > JetEngine > Query Builder, and add a new query by clicking the corresponding button.

Complete the Name field and select” SQL/AI Query” Query Type.

sql ai query type

Now move to the Custom SQL Query tab, pick a ‘jet_reviews’ option in the From table selector, and enable the Use Join toggle.

jet reviews table as a data source

We need to join a ‘posts’ table using the “Inner Join” option as a Join Type because it contains columns by which the query needs to be performed to retrieve the proper data.

The When current table column and Is equal to other table column should be set to “ID” and “post_id” respectively.

posts join tables

Proceed with adding Where (query clauses.)

In the first clause, we need to specify that the author of the post which has been reviewed is the queried user. This way, we get the posts added by the queried user.

So, the Column should be set to “posts.post_author”, Compare to “Equal,” Value to “Queried user ID,” and Type to “Numeric.”

post author query clause

In the following clause, we set “posts.post_type” as Column, “Equal” as Compare operator, and a specific post type’s slug from which we want to pull the posts as Value.

post type query clause

In the following clause, we want to eliminate the reviews submitted by the queried user themselves. This step is optional if you wish to include the reviews submitted by the post author in their own posts.

post author reviews query clause

The custom query is completed; press the “Add Query” button to save it.

Display Average Reviews Count

At this point, go to a template used on the Single User Profile Page in the Profile Builder, and add the widgets that can display the average rating count.

Add the Heading widget to the template, and click the dynamic tag icon.

dynamic tags button in the heading widget

In the dynamic tags list, select the “Dynamic Function” option.

The Function should be set to “SQL Query Results.”

The “rating” from the SQL query created in the previous step needs to be chosen as Query Column.

The Additional Function should be set to “Average” as we need to count the average rating value.

sql query results dynamic function in heading widget

After setting up the dynamic function, you can add the Before and After text in the Advanced tab to create a more customized result.

dynamic function advanced tab

Now add another Heading widget to display the number of reviews left for the posts authored by the queried user.

This time, we should select a “Query Results Count” macro and choose the Custom Query created earlier. 

The Returned Count option does not need to be changed; the default one is correct. In this Heading widget, we also added a Before and After text in the Advanced tab.

query results count dynamic tag in heading widget

Check Result on Front End

Now the template should be saved, and the result for this step can be checked on the Single User Profile Page on the front end. The Heading widgets show the average rating count and the review count correctly.

single user profile page with an average rating and review count

We can proceed with displaying the reviews created for the posts of the queried user.

Build Listing Template for SQL Query

In this step, we will create a Listing template for the custom query built to show all information about the reviews submitted to the posts of the queried user.

Go to WordPress Dashboard > JetEngine > Listings, and press the “Add New” button at the top of the page to call a Listing item settings pop-up.

The Listing source should be set to “Query Builder.” Next, pick the custom Query created earlier, and give it a name in the corresponding field.

sql ai query type

In the editor, you can add a Dynamic Field widget to show the data from the query.

Warning

You should select fields under the Query object to pull data from the custom query.

dynamic rating field in elementor

You can add any needed fields from the custom query as a Dynamic Field Source to display the reviews data.

In the described case, we added a Dynamic Field with an “author” source for showing the review’s author.

The “author” object field itself stores an ID of the user, which is why an additional callback is needed to convert a user ID into the needed user property like a Display name. The “Get user data by ID” Callback is not available by default, so it needs to be installed from GitHub Gist and uploaded to your website to be in the list of Dynamic Field Callbacks.

dynamic author field in elementor

Using another Dynamic Field widget, we also displayed a date when some user left a review. We added a “Format date” Callback to apply a needed format to date.

dynamic date field in elementor

As the Listing template is completed, it can be saved.

Output Listing Grid with SQL Query

The last step is placing the Listing Grid based on SQL query on the Single User Page.

Go to the needed template applied in the Profile Builder settings, and open it in the Elementor editor. Add the Listing Grid widget on the template, select the Listing template for the custom SQL Query, and configure other settings for the Listing Grid widget if needed.

posts reviews listing grid in elementor

Now we can check the result.

The Listing Grid shows the reviews submitted to the posts of the queried user on their Single User Profile Page as per the custom query created via JetEngine’s Query Builder.

reviews submitted to the posts of the queried user

That’s all; now you know how to display the WordPress user’s posts reviews with average rating count on the Single User Profile Page with JetReviews and JetEngine.

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.