[Resolved] Filter view results to only display parents that have children item count > 0

[Author]

Posts

[webD-3]

I am trying to get a list of counties in a state that have videos assigned to the county category/page. I don't want to list counties that have no videos assigned to them, so if the children of the parent is equal to 0, I want it filtered out from the result.

[Christian Cox]

Hi, it depends on how your Videos, States and Counties are set up - are these Custom Post Types, or a combination of CPTs and Taxonomies, or something else? Are there parent / child relationships established, or some other hierarchy? Please help clarify how all these are related. Thanks!

[webD-3]

These relationships are setup as post types and they are setup with parent/child relationships.

The video post type is a child of the county post type, and the county post type is a child of the state post type. When I go to the state page that lists the counties, it currently lists all counties in that state. I don't want people to have to sort through all counties to find videos, when some counties don't have any videos assigned to them.

[Christian Cox]

Got it, thanks. The best way to handle this is to add some custom PHP code. We offer a filter that will allow you to modify the WP Query before it's submitted. We can use the 'post__in' parameter to restrict search results to a subset of IDs, which we can build using County IDs from the postmeta table. Don't worry if that sounds like gibberish, I just wanted to give you more background information if you're interested.

Please add the following code in your functions.php file:

add_filter('wpv_filter_query', 'parent_has_childs_func', 101, 3);

function parent_has_childs_func($query, $view_settings, $view_id) {
 if ( $view_id == XX ) {
  global $wpdb;
  $ids = $wpdb->get_results( "SELECT meta_value FROM wp_postmeta WHERE meta_key = '_wpcf_belongs_city_id'" );
  $query['post__in'] = array($ids);
 }
 return $query;
}

Change "XX" to the numeric ID of your View. You can find that in the URL when you edit the View. Change '_wpcf_belong_city_id' to include your county slug instead of 'city'. For example, if your county slug is "county" then your meta key will become '_wpcf_belong_county_id'. If you use custom table prefixes for your database tables, you should change "wp_postmeta" accordingly.

More information about querying parent and child posts can be found here:
https://toolset.com/documentation/user-guides/querying-and-displaying-child-posts/

More information about the wpv_filter_query hook can be found here: https://toolset.com/documentation/programmer-reference/views-filters/#wpv_filter_query

Let me know how it goes.

[webD-3]

I added this to my functions.php file and now it's not returning any counties on the states page. This is what I entered:

add_filter('wpv_filter_query', 'parent_has_childs_func', 101, 3);

function parent_has_childs_func($query, $view_settings, $view_id) {
    if ( $view_id == 1303 ) {
        global $wpdb;
        $ids = $wpdb->get_results( "SELECT meta_value FROM wp_postmeta WHERE meta_key = '_wpcf_belongs_county_id'" );
        $query['post__in'] = array($ids);
    }
    return $query;
}

"county" is the slug for the County post type. 1303 is the id for Counties Db View.

[Christian Cox]

May I take a look at your wp-admin area? I would like to see how things are set up in here, and if necessary make a clone of your site so I can run tests locally. If that's okay with you, please provide login credentials in the private reply fields here.

[Christian Cox]

Wait, this won't work either - it doesn't respect the current state. Please standby and I will update this shortly.

[Christian Cox]

Okay I made the necessary change, sorry for the confusion. Please find the updated code here:

add_filter('wpv_filter_query', 'parent_has_childs_func', 101, 3);
function parent_has_childs_func($query, $view_settings, $view_id) {
    if ( $view_id == 1303 ) {
        global $wpdb;
        $ids = $wpdb->get_results( "SELECT meta_value FROM wp_postmeta WHERE meta_key = '_wpcf_belongs_county_id'" );
        if(isset($ids[0]->meta_value)) {
          $query['post__in'] = array_intersect($query['post__in'], array($ids[0]->meta_value) );
        }
    }
    return $query;
}

In my local testing I see no results found in Kentucky, and I see Marion County and the related Video in Indiana. Can you confirm?

[webD-3]

Thanks for your help! That solved the problem and it's filtering them out now!

[webD-3]

Sorry to bother you guys again, but I noticed when I added a second video to a county, it doesn't show up in the results. It still only shows the one result, instead of two counties containing videos. I tried removing the script from the functions.php file, and both results did show up in the long list.

I also tried placing it in a different state, and it didn't show any results in that state still with this filter in the functions.php file.

[Christian Cox]

No problem, I think we're really close to getting this perfect. Please replace your code again with this change, which should correct the problem where only the first county appears for a state:

add_filter('wpv_filter_query', 'parent_has_childs_func', 101, 3);
function parent_has_childs_func($query, $view_settings, $view_id) {
    if ( $view_id == 1303 ) {
        global $wpdb;
        $ids = array();
        $metas = $wpdb->get_results( "SELECT meta_value FROM wp_postmeta WHERE meta_key = '_wpcf_belongs_county_id'");
        foreach($metas as $id) {
          if(isset($id->meta_value)) {
            $ids[] = $id->meta_value;
          }
        }
        $query['post__in'] = array_intersect($query['post__in'], $ids );
    }
    return $query;
}

At this point you should be able to add Videos to multiple counties in the same state and have them appear in your list. Be careful though - you've got some duplicate county names. It would be easy to select Marion County, Kentucky instead of Marion County, Indiana if you're using the Post Relationship area in the Video editor. It's probably most practical to create Videos from the County editor instead, then edit them in Video editor.

I'm able to add videos to other states as well. Can you confirm? Screenshots attached.

[webD-3]

Is that the exact code you had? I'm having an issue with it. My editor is giving me a syntax error: unexpected token ']' after $ids[] in the foreach loop. I see we have an opening " and ' after "get_results", but I don't see where they were closed...is that part of the issue?

[webD-3]

I believe I got it adjusted. I changed the WHERE statement to WHERE meta_key = '_wpcf_belongs_county_id'");

Thanks!

[Christian Cox]

You got it, my editor truncated that line and I didn't realize. Glad this is working for you! Feel free to reopen if you find any other bugs.

[Adrian Alvarez]

This is great. On the same vein, what would the code need to be to display only parents who have 0 children?