How to query for posts (hierarchical custom post type) that have children but are NOT top level?

Question

I'm trying to create a custom filter for a custom post type that will display only posts that have children – post_parent__not_in => 0 won't work because these posts have parents. I'm trying to target posts "in the middle", not top level posts.

This has to be just simple query (no checking in the loop) as it will be used with the pre_get_posts hook.

Answer 1

You can use filters to modify the SQL. I added something unique in the post_type so I could filter only this query and not change anything else.

The query for example

$query = new WP_Query(array('post_type'=>'something_unique_for_filter'));

And the filter

function custom_where($where, $query) {
    global $wpdb;
    if(false !== strpos($where, 'something_unique_for_filter')) {
        $where = " AND post_type='page' AND post_parent != 0 AND ID IN (SELECT DISTINCT post_parent FROM $wpdb->posts WHERE post_type='page')";
    }
    return $where;
}
add_filter('posts_where', 'custom_where', 10, 2);

To add support for meta query first step is to add meta_key to the WP_Query so it INNER JOIN the postmeta table.

$query = new WP_Query(array('post_type'=>'something_unique_for_filter', 'meta_key'=>'something'));

And the filter with meta

function custom_where($where, $query) {
    global $wpdb;
    if(false !== strpos($where, 'something_unique_for_filter')) {
        $where = " AND ( $wpdb->postmeta.meta_key = 'test' AND $wpdb->postmeta.meta_value = '123' )
        AND post_type='page' AND post_parent != 0
        AND ID IN (SELECT DISTINCT post_parent FROM $wpdb->posts WHERE post_type='page')";
    }
    return $where;
}
add_filter('posts_where', 'custom_where', 10, 2);