Filtering WordPress children posts by parents taxonomy using WP_Query?

Question

I have a custom events post type in WordPress (with recurring events being saved as children to the main event) I have a few custom taxonomies set up which only saves the custom taxonomy data to the parent event post, but what I am trying to figure out is how to filter the results (including the recurring children posts) by the custom taxonomies. At current I have something similar to:

$args = array(
    'post_type'     => 'incsub_event',
    'posts_per_page'    => 50,
    'post_status' => array( 'recurrent', 'publish'),
    'meta_query'        => array(
        array(
            'key' => 'incsub_event_start',
            'value' => array( $date_1, $date_2 ),
            'type' => 'DATETIME',
            'compare' => 'BETWEEN'
        ),
        array(
            'key' => 'incsub_event_fee',
            'value' => array( '10', '1000' ),
            'type' => 'NUMERIC',
            'compare' => 'BETWEEN'
        ),
        array(
            'key' => 'incsub_event_status',
            'value' => 'open',
            'type' => 'BINARY',
            'compare' => '='
        ),
    ),
    'tax_query' => array(
        array(
            'taxonomy' => 'location',
            'field'    => 'slug',
            'terms'    => 'uk',
        ),
    ),
    'order'     => 'ASC',
    'orderby'   => 'meta_value',
    'meta_key'  => 'incsub_event_start' 
);

But it will only return the parent posts not the children, I would be grateful to anyone who could shed some help on the matter?

kellanburket kellanburket · Accepted Answer · 2015-02-15T18:49:09

I had this issue a while ago and the solution isn't pretty. As far as I know, Wordpress's get_posts function doesn't support nested queries where 'parent has x-attribute'. To access the child posts you're going to have to roll your own get_posts-style function, using $wpdb to access the database. I've created a function for you that will work as the basis for this kind of query. There are probably some improvements and additions that need to be made to include other common search variables, but for your current query it will get the job done.

function customGetPosts($args) {
    global $wpdb;
    $wpdb->show_errors();
    extract($args);

    $limit = isset($posts_per_page) ? $posts_per_page : 50;
    $post_type = isset($post_type) ? $post_type : "post";
    $post_status = isset($post_status) ? $post_status : "publish";
    $post_status = is_array($post_status) ? "IN ('" . implode("' ,'", $post_status) . "')" : "= '$post_status'";
    $order = isset($order) && in_array($order, ["ASC", "DESC"]) ? $order : "DESC";
    $order = isset($orderby) && isset($meta_key) ? "ORDER BY $meta_key $order" : "";

    if (isset($meta_query) && is_array($meta_query) && count($meta_query) > 0) {
        $meta_query_string = ", ";
        $meta_query_where_string = "";
        $is_first = true;

        foreach($meta_query as $query) {
            if ($is_first) {
                $is_first = false;
                $meta_query_where_string .= "WHERE ";
            } else {
                $meta_query_where_string .= "AND ";
            }

            $meta_query_string .= "MAX(IF(wpm.meta_key = '{$query['key']}', wpm.meta_value, NULL)) AS {$query['key']}, ";

            switch($query['type']) {
                case("NUMERIC"):
                    $delimiter = "";
                    break;
                default: 
                    $delimiter = "'";
            } 

            switch ($query['compare']) {
                case("BETWEEN"):
                    $meta_query_where_string .= "{$query['key']} {$query['compare']} $delimiter{$query['value'][0]}$delimiter AND $delimiter{$query['value'][1]}$delimiter ";   
                    break;
                default: 
                    $meta_query_where_string .= "{$query['key']} {$query['compare']} $delimiter{$query['value']}$delimiter ";   
            }
        }
        $meta_query_where_string = substr($meta_query_where_string, 0, strlen($meta_query_where_string) - 1);
        $meta_query_string = substr($meta_query_string, 0, strlen($meta_query_string) - 2);
    }

    if (isset($tax_query) && is_array($tax_query) && count($tax_query) > 0) {

        foreach($tax_query as $query) {
            $terms = is_array($query['terms']) ? "'" . implode("', '", $query['terms']) . "'" : "'{$query['terms']}'";

            $tax_query_string .= "AND wtt.taxonomy = '{$query['taxonomy']}' AND wt.{$query['field']} IN ({$terms}) ";
        }

    }


    return $wpdb->get_results("
        SELECT * FROM
        (
            SELECT wp.*$meta_query_string
            FROM wp_posts wp
            JOIN wp_term_relationships wtr ON wp.ID = wtr.object_id
            JOIN wp_term_taxonomy wtt ON wtt.term_taxonomy_id = wtr.term_taxonomy_id
            JOIN wp_terms wt ON wtt.term_id = wt.term_id
            JOIN wp_postmeta wpm ON wpm.post_id = wp.ID
            WHERE wp.post_status $post_status
                $tax_query_string
                AND wp.post_type = '$post_type'
            GROUP BY wp.ID

            UNION ALL

            SELECT wp.*$meta_query_string 
            FROM wp_posts wp 
            JOIN wp_postmeta wpm ON wpm.post_id = wp.post_parent
            WHERE wp.post_parent IN (    
                SELECT wp.ID FROM wp_posts wp
                JOIN wp_term_relationships wtr ON wp.ID = wtr.object_id
                JOIN wp_term_taxonomy wtt ON wtt.term_taxonomy_id = wtr.term_taxonomy_id
                JOIN wp_terms wt ON wtt.term_id = wt.term_id
                JOIN wp_postmeta wpm ON wpm.post_id = wp.ID
                WHERE wp.post_status $post_status
                    $tax_query_string
                    AND wp.post_type = '$post_type'
            )
            GROUP BY wp.ID

        ) as main
        $meta_query_where_string
        $order
        LIMIT $limit"
    );
}

As a sidenote, your metavalues can be accessed through their metakey in the getCustomPosts output, e.g.: while iterating through the results, $post->incsub_event_start

Filtering WordPress children posts by parents taxonomy using WP_Query?

1 Answers