1

I have a taxonomy "artist" for two different custom post types ("project" and "product").

I need to display a list of "artists", but only if they have related "products", and only if those products are still in stock.

$artists = get_terms( array(
   'taxonomy' => 'artist',
   'hide_empty' => 1,
) );

This includes artists having "projects" but no "products". Is it possible to specify for which post type the "hide_empty" argument should refer to ?

EDIT Here is what I have so far.

        /*
        Useful when one taxonomy applies to multiple post types
        source: http://wordpress.stackexchange.com/a/24074/82
    */
    function get_terms_by_post_type( $taxonomies, $post_types ) {

        global $wpdb;

        $query = $wpdb->prepare(
            "SELECT t.*, COUNT(*) from $wpdb->terms AS t
            INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id
            INNER JOIN $wpdb->term_relationships AS r ON r.term_taxonomy_id = tt.term_taxonomy_id
            INNER JOIN $wpdb->posts AS p ON p.ID = r.object_id
            WHERE p.post_type IN('%s') AND tt.taxonomy IN('%s')
            GROUP BY t.term_id",
            join( "', '", $post_types ),
            join( "', '", $taxonomies )
        );

        $results = $wpdb->get_results( $query );
        return $results;
       }

$artists = get_terms_by_post_type( array('artist'), array('product'));

//*
if( !empty($artists) && !is_wp_error( $artists )  ){

    // filter out out-of-stock products artists
    foreach($artists as $k=>$artist){
        $args = array(
            'post_type'     => 'product',
            'post_status'   => 'publish',
            'posts_per_page' => -1,
            'tax_query' => array(
                'relation' => 'AND',
                array(
                    'taxonomy' => 'artist',
                    'field' => 'id',
                    'terms' => array( $artist->term_id )
                ),
                array(
                    'key' => '_stock_status',
                    'value' => 'instock'
                )
            )
        );
        $query = new WP_Query($args);
        echo $artist->slug;
        pr($query->post_count);

        if($query->post_count<1){
            unset($artists[$k]);
        }
    }
}

It almost works, but it does not remove products out of stock.

1

Thanks to this answer, I was able to see the light. Here is how to query taxonomy terms related to (woocommerce) products that are "in stock". It uses both tax_query (to relate product to taxonomy) and meta_query (to filter out out-of-stock products).

$artists = get_terms_by_post_type( array('artist'), array('product'));

if( !empty($artists) && !is_wp_error( $artists )  ){

    // filter out out-of-stock products artists
    foreach($artists as $k=>$artist){
        $args = array(
            'post_type'     => 'product',
            'post_status'   => 'publish',
            'posts_per_page' => -1,
            'meta_query' =>array(
                'relation' => 'AND',
                array(
                    'key' => '_stock_status',
                    'value' => 'instock',
                    'compare' => '='
                )
                ,
                array(
                    'key' => '_stock',
                    'value' => '0',
                    'compare' => '>'
                )
                ,
                array(
                    'key' => '_stock',
                    'value' => '',
                    'compare' => '!='
                )
            ),
            'tax_query' => array(
                'relation' => 'AND',
                array(
                    'taxonomy' => 'artist',
                    'field' => 'id',
                    'terms' => array( $artist->term_id )
                ),


            )
        );

        $query = new WP_Query($args);

        if($query->post_count<1){
            unset($artists[$k]);
        }
    }
}

function get_terms_by_post_type( $taxonomies, $post_types ) {

    global $wpdb;

    $query = $wpdb->prepare(
        "SELECT t.*, COUNT(*) from $wpdb->terms AS t
        INNER JOIN $wpdb->term_taxonomy AS tt ON t.term_id = tt.term_id
        INNER JOIN $wpdb->term_relationships AS r ON r.term_taxonomy_id = tt.term_taxonomy_id
        INNER JOIN $wpdb->posts AS p ON p.ID = r.object_id
        WHERE p.post_type IN('%s') AND tt.taxonomy IN('%s')
        GROUP BY t.term_id",
        join( "', '", $post_types ),
        join( "', '", $taxonomies )
    );

    $results = $wpdb->get_results( $query );

    return $results;

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.