John 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.
https://formidableforms.com/knowledgebase/frm_view_order/#kb-create-a-leaderboard
https://formidableforms.com/knowledgebase/frm_view_order/#kb-order-by-month

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

Thanks,
John

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 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 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;
}

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

Cheers,
John

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.