Select query with two and two related taxonomies

Question

is this possible for 2 taxonomy and 2 terms

 $querystr = "
 SELECT *
 FROM $wpdb->posts
 LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
 LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
 LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
 WHERE $wpdb->posts.post_type = 'shows'
 AND $wpdb->posts.post_status = 'publish'
 AND $wpdb->term_taxonomy.taxonomy = 'location'
 AND $wpdb->term_taxonomy.taxonomy = 'genre'
 AND $wpdb->terms.slug = 'california'
 AND $wpdb->terms.slug = 'comedy'
 ORDER BY $wpdb->posts.post_date DESC
 LIMIT 10
 ";

$pageposts = $wpdb->get_results($querystr, OBJECT);

if ($pposts):

foreach ($pposts as $post):
setup_postdata($post);

the_title();
echo '<br'>;
the_content();

endforeach;
endif;

i tried putting this code above but the doesnt display anything; I want to display posts that has 2 term name and 2 taxonomy

like for example: sampleposts [showlocation_tax][genre_tax]

showlocation_tax has terms

-california -LA

while genre_tax - comedy - fashion

what i want to do is i want to show all post for example:

posts from california with comedy shows something like that

i will really appreciate your help a lot

Answer 1

I believe you can just use the WP_Query method to query the database with a tax_query like so:

$my_query_args = array(
    'post_type' => 'shows',
    'tax_query' => array(
        'relation' => 'AND',
        array(
            'taxonomy' => 'location',
            'field' => 'slug',
            'terms' => 'california'
        ),
        array(
            'taxonomy' => 'genre',
            'field' => 'slug',
            'terms' => 'comedy'
        )
    )
);

$my_query = new WP_Query( $my_query_args );

if( $my_query->have_posts() ) : while( $my_query->have_posts() ) : $my_query->the_post();

    the_title();
    echo '<br'>;
    the_content();  

endwhile; endif; wp_reset_postdata();

Answer 2

Here's a function i used to do a query with 2 custom taxonomies and 2 terms:

in functions.php

// MULTIPLE TAXONOMY QUERY ALLOWANCE
function posts_search ($post_type,$taxonomies) { // $taxonomies should be an array ('taxonomy'=>'term', 'taxonomy2'=>'term2')
    foreach ($taxonomies as $key=>$value) {
        $args=array('post_type'=>$post_type,'post__in'=>$ids,$key=>$value);
        unset($ids); $ids=array();
        foreach($posts=get_posts($args) as $post) { $ids[]=$post->ID; }
        if (empty($ids)) return false;
    }
    return $posts;
}

and in a template file:

$posts = posts_search ('produtos',array('prod-categoria'=>$tt,'prod-cols'=>'5-C-P-F-NF-P')); 
if($posts) { 

    foreach($posts as $post) { 

        if(get_post_meta($post->ID, "codigo_value", $single = true) != "") { 

            //here you can retrieve infos; i was interested in custom fields in my case
            echo get_post_meta($post->ID, "codigo_value", $single = true); 

        }

    } 
}

My two custom taxonomies here are prod-categoria and prod-cols and i'm looking for two terms, one of which comes from a variable $tt and the other 5-C-P-F-NF-P.

Hope that helps.