I want to sort VIEW A by a sum in FORM B

By: John Young | Asked: 05/30/2022
ForumsCategory: General questionsI want to sort VIEW A by a sum in FORM B
John YoungJohn Young asked 2 years ago

I have a membership site where visitors can leave a review of any member. I want to sort the Member Directory (View A built from Form A) by the SUM of reviews (Entries in Form for each member.
Note that Member Profile is in Form A and Review data is in Form B.

Example: Member1 has 5 reviews, Member2 has 1 review and Member3 has 10 reviews.
The Member Directory should be sorted then as:
1st: Member 3 (10 reviews)
2nd: Member 1 (5 reviews)
3rd: Member 2 (1 review)

Form A = Member Profile
- Members create their profile

View A = Member Directory
- A list of all the members who have profiles in Form A

Form B = Member Review
- Visitors can leave a review of the member

Joins: Member Profile (Form A) joins to Member Review (Form B ) on A.USER_ID = B.USER_ID

Bobby Clapp replied 2 years ago

Does form B collect the member information from form A? I'm guessing not, but need clarity.

John YoungJohn Young replied 2 years ago

Hi Bobby,

You are correct. There is no data passed between Form A and Form B. I will do my best to describe the data flow below.

1. After a member registers they can navigate to Form A (Member Profile) to create their profile by submitting the form.
2. Their profile is displayed in a list on a member directory page (View built from Form A. ).
3. Member profile view has a button to leave a review. When clicked:
4. They are redirected to the review form (Form B. ). The User_ID of the member profile they clicked on is passed as a URL parameter to the review form.
5. After the review is submitted, any reviews left for the user are displayed on the member profile by way of a nested view (view B. created from form B. ).

I want to sort the member directory view by the count of reviews left for each user.

I know it's going to have to be a PHP snippet with SQL in it, but unfortunately I'm still learning PHP and the formidable data dictionary is a little confusing haha.
Any help would be appreciated. Even if you can perhaps template some PHP and I can try to tweak and fill in the blanks if that's what it takes. I use all these opportunities for learning and would appreciate it greatly!

Kind regards,

2 Answers
Victor Font answered 2 years ago
What you're trying to accomplish is complicated, but can be done using the frm_view_order filter. This filter, when applied to your Form A view will allow you to sort the content as you desire. Take a look at the "Create a Leaderboard" example in the referenced knowledge base article. This should get you started down the right path.
John YoungJohn Young replied 2 years ago

Hi @Victor Font - You are right it was incredibly complex. To simplify the process and reduce load on the DB as the member base grows, I've created a stored proc that creates the leaderboard for me once a day.

I've got the leaderboard table populating great and ordered by the field: total, however I cannot for the LIFE of me work out how to get the change_view_order working???

leaderboard table name: leaderboard
columns: user_id, total
Sorted by: total DESC

I want to order my member directory view (3628) by the order of the leaderboard table.

I've tried varients of both below but every time I enable the snippet and refresh the member page it says NO ENTRIES.

If you could provide some guidance here it would be greatly appreciated.


Victor Font replied 2 years ago

The frm_view_order is extremely powerful because you can substitute Formidable generated SQL with your own custom code. All the sorting can take place in your query or pre-query calculations. The query must return only a list of entry IDs that match your search criteria. If you are returning user_id and total, it will not work.

I regularly use frm_view_order to write complex SQL queries that are beyond the scope of what Formidable can produce. Because you can write your own query, you can use SQL views, stored procedures, alternate tables, or any other SQL enhancement you'd like as long as it's supported by your database version. You must always return entry ids though as the values to pass to the view for display. The view will display the entries in the order they appear in the query output.

John YoungJohn Young replied 2 years ago

Hi @Victor Font

Thanks for the helpful information!

I have since updated my leaderboard table to store ID, USER_ID, TOTAL in DESC order. running from phpmyadmin confirms it.
These IDs match the entry id's in the view.

As the leaderboard table already contains all the info needed in the correct order, performing a simple select id from leaderboard should be sufficient based on your explanation.

I have tried many different combinations of SQL in the php code and the view continues to show NO ENTRIES FOUND so I am assuming that there is an issue with my syntax.

Could you advise on below PHP and tell me if i've made an obvious mistake?

add_filter('frm_view_order', 'change_view_order', 10, 2);
function change_view_order($query, $args){
if ( $args['display']->ID == 3628 ) { //custom view ID
$query['select'] = "SELECT id FROM leaderboard";
return $query;

John YoungJohn Young replied 2 years ago

@Victor Font another iteration which is returning no results in the view, but returns perfect results in phpmyadmin

add_filter('frm_view_order', 'change_view_order', 10, 2);
function change_view_order($query, $args){
if ( $args['display']->ID == 3628 ) { //custom view ID
//return all members with a profile even if they are not in the leaderboard yet.
$query['select'] = "select it.id from wpdh_frm_items it left join leaderboard ld on ld.id = it.id";
//id of member profile form to return all members with profiles.
$query['where'] = "where it.form_id = 18";
return $query;

Victor Font answered 2 years ago
It's hard to say what could be wrong without actually debugging your site, but it's not with your SQL. Your code just needs some slight adjustments. Here are some tips about using the frm_view_order filter that could change your result. When I'm doing a deep dive into code, it helps tremendously to be able to view hook parameters in real time using the Kint debugger. It's available in the WordPress repository from a few sources. I use the one that was authored by Tonya Mork, a WordPress Core team member. She hasn't released an update to this plugin in awhile and the code won't run correctly on PHP 8+ because of how some arrays are initialized. I edited the version I use so it works up to 8.1. I don't know that I can get it done today, but I'll make my version available as a free download on the Masterminds site soon. Kint gives me a better understanding about how to best work with Formidable. The attached image is a screen capture of the Kint debugger's output after I set a breakpoint and halted site execution to view the $query parameter as passed to the frm_view_order callback by the filter when I clicked the Search Directory menu item. I did this on the Formidable Masterminds site just as I started to write this response. It's my actual production site in real time. This image displays the $query array contents when the directory view first loads all entry records from the database. This is entirely Formidable generated and the first thing we need to understood in depth before writing custom code for this filter. The $query array has 3 elements, select, where, and order. The select is a simple SQL SELECT that returns ids from wp_frm_items. Notice that $query['where'] is an array of variables and one of those variables is also an array. $order is a standard SQL ORDER clause where the records are returned in the order of creation time and id. When executed this SQL returns entry ids from the header records which are then used to construct the view output. Now the question to investigate becomes, "Why are the Formidable developers using an array for $query['where']?". $query['where'] consists of three elements, it.id, which is also an array that appears to contain entry ids. It also has it.is_draft and it.form_id fields. From the SELECT, we know that 'it.' is the alias the developers assigned to the wp_frm_items table. Getting to the root of $query['where'] requires looking at Formidable's code where the frm_view_order filter is applied. We need to understand all the code that comes before and after the filter statement. I'll reserve those details for the article. To help you along, here is the code I actually use for the Masterminds Directory Search form. https://gist.github.com/vfontjr/85136cf1fe0fa4a1febdf401d4c20e4a#file-frm_view_order_callback-php. The built-on-the-fly SQL WHERE clause is a little complex. It switches between AND and OR as well as adding a variable number of tables and joins on the fly. What you should focus on is how the code empty's $query['where'] and concatenates the custom where clause to the to $query['select']. That should set you in the right direction.
John YoungJohn Young replied 2 years ago

@Victor Font - Thank you very much for such a detailed response and sharing your snippet for reference.

From your info provided I've managed to achieve the previously unachievable! 🙂 I can now sort my members via sql against a secondary table.

My SQL was solid, but my understanding of the formidable PHP query syntax and requirements was off.

Truly appreciate your guidance. You offer enough for me to learn through exercise but guided in the right direction. Have learned a lot about wordpress, formidable and PHP through this requirement alone.

I'll put togethor a tutorial on my findings as well to help others who might have a similar requirement.


Victor Font replied 2 years ago

That's great news John! Glad to help. When you finish your tutorial, please feel free to post a link to it in the Formidable Masterminds Facebook group. I'm sure you'll be able to help many others looking for a similar solution.

Making the Best WordPress Plugin even better - Together

Take on bigger projects with confidence knowing you have access to an entire community of Formidable Experts and Professionals who have your back when the going gets tough. You got this!
Join the community