How do I query taxonomy OR custom field in WordPress at the same time?

1 Views

I need to query wordpress posts returning results that could be matched either by the category OR custom field. Using the below code only returns results if they are matched by the category AND custom field. I need to find matches where it could be matched by either query, so an “OR” result but doesn’t seem to work.

Can anyone help?

$args =  array(
'post_type' => 'post',
'tax_query' => array(
'relation' => 'OR',
    array(
        'taxonomy'  => 'category',
        'field'     => 'slug',
        'terms' => array( 'arts','food' ),
        'operator'  => 'IN'
    )
),
'meta_query' => array(
   'relation' => 'OR',
    array(
        'key' => 'birth_city',
        'value' => 'London',
        'compare' => 'IN',
      ),
   )
);
Post Views: 1

Related posts

Leave a Reply

3 comments

  1. Version 0.2

    Here’s a simplification of my previous answer – I hope you don’t mind me adding it as a new answer, to avoid confusion with my previous one.

    Here I use the WordPress functions get_meta_sql() and get_tax_sql(), to modify the WHERE part of the SQL generated by WP_Query():

     /**
 * Class WPSE_OR_Query 
 * 
 * Add a support for (tax_query OR meta_query) 
 *
 * @version 0.2
 * @link http://stackoverflow.com/a/22633399/2078474
 *
 */
class WPSE_OR_Query extends WP_Query 
{       
    protected $meta_or_tax  = FALSE;
    protected $tax_args     = NULL;
    protected $meta_args    = NULL;

    public function __construct( $args = array() )
    {
        add_action( 'pre_get_posts', array( $this, 'pre_get_posts' ), 999 );
        add_filter( 'posts_where',   array( $this, 'posts_where' ),   999 );
        parent::__construct( $args );
    }
    public function pre_get_posts( $qry )
    {       
        remove_action( current_filter(), array( $this, __FUNCTION__ ) );            
        // Get query vars
        $this->meta_or_tax = ( isset( $qry->query_vars['meta_or_tax'] ) ) ? $qry->query_vars['meta_or_tax'] : FALSE;
        if( $this->meta_or_tax )
        { 
            $this->tax_args  = ( isset( $qry->query_vars['tax_query'] ) )  ? $qry->query_vars['tax_query']  : NULL;
            $this->meta_args = ( isset( $qry->query_vars['meta_query'] ) ) ? $qry->query_vars['meta_query'] : NULL;
        }
    }
    public function posts_where( $where )
    {       
        global $wpdb;       
        $field = 'ID';
        remove_filter( current_filter(), array( $this, __FUNCTION__ ) );    

        // Construct the "tax OR meta" query
        if( $this->meta_or_tax && is_array( $this->tax_args ) &&  is_array( $this->meta_args )  )
        {
            // Tax query
            $sql_tax = get_tax_sql( $this->tax_args, $wpdb->posts, $field );            

            // Meta query
            $sql_meta = get_meta_sql( $this->meta_args, 'post', $wpdb->posts, $field );

            // Modify the 'where' part          
            if( isset( $sql_meta['where'] ) && isset( $sql_tax['where'] ) )
            {
                $where  = str_replace( array( $sql_meta['where'], $sql_tax['where'] ) , '', $where );
                $where .= sprintf( ' AND ( %s OR  %s ) ', substr( trim( $sql_meta['where'] ), 4 ), substr( trim( $sql_tax['where'] ), 4 ) );              
            }
        }   
        return $where;
    }
}

  2. Here’s the first attempt to solve your problem. I worked within the following restrictions:

    • don’t write the SQL query directly
    • extend the WP_Query
    • use WP_Tax_Query and WP_Meta_Query
    • don’t use preg_replace/str_replace on the SQL query.

    This is maybe too restrict, but it’s a challange 😉

    So you could try:

    $args =  array(
    'post_type' => 'post',
    'meta_or_tax' => TRUE,
    'tax_query' => array(
            array(
                'taxonomy'  => 'category',
                'field'     => 'slug',
                'terms' => array( 'birds','falcons' ),
                'operator'  => 'IN'
            )
    ),
    'meta_query' => array(
            array(
                'key' => 'birth_city',
                'value' => 'London',
                'compare' => 'IN',
            ),
    )
);

$q = new WPSE_OR_Query( $args );

    where we introduce the new parameter meta_or_tax:

    `meta_or_tax` => FALSE

    will give you the normal WP_Query behaviour and

    `meta_or_tax` => TRUE

    will allow you to get OR instead of the default AND between the tax query and the meta query.

    The new WPSE_OR_Query class is defined as:

     /**
 * Class WPSE_OR_Query
 *
 * Add a support for (tax_query OR meta_query) 
 *
 * @link http://stackoverflow.com/a/22616337/2078474
 *
 */
class WPSE_OR_Query extends WP_Query 
{       
    protected $meta_or_tax  = FALSE;
    protected $tax_args     = NULL;
    protected $meta_args    = NULL;

    public function __construct( $args = array() )
    {
        add_action( 'pre_get_posts', array( $this, 'pre_get_posts' ), 10 );
        add_filter( 'posts_clauses', array( $this, 'posts_clauses' ), 10 );
        parent::__construct( $args );
    }

    public function pre_get_posts( $qry )
    {       
        remove_action( current_filter(), array( $this, __FUNCTION__ ) );            
        // Get query vars
        $this->meta_or_tax = ( isset( $qry->query_vars['meta_or_tax'] ) ) ? $qry->query_vars['meta_or_tax'] : FALSE;
        if( $this->meta_or_tax )
        { 
            $this->tax_args = ( isset( $qry->query_vars['tax_query'] ) ) ? $qry->query_vars['tax_query'] : NULL;
            $this->meta_args = ( isset( $qry->query_vars['meta_query'] ) ) ? $qry->query_vars['meta_query'] : NULL;
            // Unset meta and tax query
            unset( $qry->query_vars['meta_query'] );
            unset( $qry->query_vars['tax_query'] );
        }
    }

    public function posts_clauses( $clauses )
    {       
        global $wpdb;       
        $field = 'ID';
        remove_filter( current_filter(), array( $this, __FUNCTION__ ) );    
        // Reconstruct the "tax OR meta" query
        if( $this->meta_or_tax && is_array( $this->tax_args ) &&  is_array( $this->meta_args )  )
        {
            // Tax query
            $tax_query = new WP_Tax_Query( $this->tax_args );
            $sql_tax = $tax_query->get_sql( $wpdb->posts, $field );
            // Meta query
            $meta_query = new WP_Meta_Query( $this->meta_args );
            $sql_meta = $meta_query->get_sql( 'post', $wpdb->posts, $field );
            // Where part
            if( isset( $sql_meta['where'] ) && isset( $sql_tax['where'] ) )
            {
                $t = substr( trim( $sql_tax['where'] ), 4 );
                $m = substr( trim( $sql_meta['where'] ), 4 );
                $clauses['where'] .= sprintf( ' AND ( %s OR  %s ) ', $t, $m );              
            }
            // Join/Groupby part
            if( isset( $sql_meta['join'] ) && isset( $sql_tax['join'] ) )
            {
                $clauses['join']    .= sprintf( ' %s %s ', $sql_meta['join'], $sql_tax['join'] );               
                $clauses['groupby'] .= sprintf( ' %s.%s ', $wpdb->posts, $field );
            }       
        }   
        return $clauses;
    }

}

    Here’s an example of the normal SQL:

    SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
WHERE 1=1 
AND ( wp_term_relationships.term_taxonomy_id IN (15,70) )
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND ( (wp_postmeta.meta_key = 'birth_city' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('London')) ) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

    and the SQL of the modified version:

    SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) 
WHERE 1=1 
AND wp_posts.post_type = 'post' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
AND ( ( (wp_postmeta.meta_key = 'birth_city' AND CAST(wp_postmeta.meta_value AS CHAR) IN ('London')) ) OR ( wp_term_relationships.term_taxonomy_id IN (15,70) ) ) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

    This can of course be refined much more and you can hopefully extend it further to your needs.

    Hope this help.

  3. I failed to find a way of how to do it with WP_Query, but if it helps, here is how to manually write the query, it looks ugly but if that is the only way it will serve the purpose:

        $results = $wpdb->get_results( $wpdb->prepare( "
        SELECT * FROM $wpdb->posts
        LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
        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_status = 'publish'
        AND $wpdb->posts.post_type = 'post' 
        AND $wpdb->term_taxonomy.taxonomy = 'category'                  
        AND $wpdb->terms.name IN ( %s, %s ) 
        OR ( $wpdb->postmeta.meta_key = %s
        AND $wpdb->postmeta.meta_value = %s )
        ORDER BY $wpdb->posts.post_date DESC
    ", 'arts', 'food', 'birth_city', 'London' ), OBJECT_K );