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.
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.
Now move to the Custom SQL Query tab, pick a ‘jet_reviews’ option in the From table selector, and enable the Use Join toggle.
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.
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.”
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.
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.
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.
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.
After setting up the dynamic function, you can add the Before and After text in the Advanced tab to create a more customized result.
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.
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.
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.
In the editor, you can add a Dynamic Field widget to show the data from the query.
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.
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.
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.
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.
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.