Advanced or not so advanced pre_get_posts query

Before amending $query we first have to find out which posts to exclude, which requres two querys (if I can think of a way of doing it in one I’ll update the answer).

The first will grab a list of distinct ID’s of all parent events, for example –

SELECT DISTINCT wp_posts.post_parent FROM wp_posts WHERE wp_posts.post_type = "event" AND wp_posts.post_type = "publish" AND wp_posts.post_parent != 0

The next will grab the ID of all top-level events that do not have any children (so those that are not in the list we generated with the previous query), for example –

SELECT fgw_2_posts.ID FROM wp_posts WHERE wp_posts.post_type = "event" AND wp_posts.post_type = "publish" AND wp_posts.post_parent = 0 AND wp_posts.ID NOT IN (1,2,3)

Finally we can set the $query as required –

$query->set('post__not_in', $loners);
$query->set('post_parent', 0);

Here is the full code –

add_action('pre_get_posts', 'my_exclude_parents_without_children');
function my_exclude_parents_without_children($query){

    global $wpdb;

    if($query->is_main_query() && !is_admin()) :

        if(is_post_type_archive('event')) :

            /** First grab the ID's of all post parents */
            $my_query = $wpdb->prepare('SELECT DISTINCT %1$s.post_parent FROM %1$s WHERE %1$s.post_type = "event" AND %1$s.post_status = "publish" AND %1$s.post_parent != 0', $wpdb->posts);
            $parents = $wpdb->get_col($my_query);

            /** Next grab the ID of all posts who do not have children (we'll call them 'loners') */
            $my_query = $wpdb->prepare('SELECT %1$s.ID FROM %1$s WHERE %1$s.post_type = "event" AND %1$s.post_status = "publish" AND %1$s.post_parent = 0', $wpdb->posts, join(',', $parents));
            if(!empty($parents)) : // Ensure that there are events with children to exclude from this query
                $my_query.= $wpdb->prepare(' AND %1$s.ID NOT IN (%2$s)', $wpdb->posts, join(',', $parents));
            endif;
            $loners = $wpdb->get_col($my_query);

            /** Now exclude the 'loners' */
            $query->set('post__not_in', $loners);
            $query->set('post_parent', 0);

        endif;

    endif;

}