Hi,
I’m attempting to develop a View that essentially needs to display, filter, and order by fields across two related post types. The app we’re developing with Toolset is replacing an older version built in ColdFusion. In the old app, the query is accomplished via an INNER JOIN between two related database tables.
In our WordPress/Toolset setup, here’s what we’ve got:
1. Custom Post Type “Aid Accounts”, with custom fields account-id, account-name, account-number, is-active (checkbox, 1 for active, 0 for not active)
2. Custom Post Type “Aid Yearly Totals” with custom fields account-id, aid-year, aid-amount
3. Relationship “Aid Accounts Aid Yearly Totals”, which relates one Aid Account post to many Aid Yearly Totals posts (in plain English, one Aid Account, will be assigned different totals for different years.
4. Function using wpv_filter_query (Not developed yet, this is where I'm getting stuck.)
I tried to follow some documentation I found at https://toolset.com/forums/topic/run-custom-sql-query-and-attach-or-pass-to-view/ and some others I followed from references in that thread, but haven’t been successful.
Based on the above, my understanding is that to pull off the equivalent of what would be an INNER JOIN query in regular SQL, I need to set up a view that queries one of the custom post types, with no Query Filter setting in the View UI. But not sure which post type it’s best to query (parent or child in the one-to-many relationshp), and how to write the function that will get me the data I need from both related custom post types. Here’s what I’ve got:
add_filter( 'wpv_filter_query', 'aid_account_data', 99, 3 );
function aid_account_data( $query_args, $view_settings, $views_id ) {
if ( $views_id == 31028 && !isset($_GET['wpv_filter_submit']) ) { // ID of the view
// Not sure how to write this part of the function.
}
return $query_args;
}
Practically speaking, here’s what I need to do with the View:
* Join Aid Accounts to related Aid Yearly Totals (joined on account-id). Relationships are already successfully established.
* Filter the view by Aid Yearly Totals aid-year (could be in querystring), and then only show active accounts (Aid Accounts is-active)
* Loop through the filtered posts and display:
* Aid Accounts account-number
* Aid Accounts account-name
* Aid Yearly Totals aid-amount
* Order by Aid Account account-name
Here's the URL to our page-in-progress: hidden link
But it's not filtering (both inactive and active accounts are showing, and they'll need to filter by year as well). I'm showing field values from Aid Accounts, but don't know how to get the related data for the amount from the related Aid Yearly Totals field.
Thanks for the support,
David
