0

I need to search property post types, in a regular WP_Query that is not the main query, but I need to be able to search for a value LIKE in any of the following meta_queries and also need to search LIKE in tax_queries, but the comparison between tax_query and meta_query should NOT be an AND, it needs to be an OR. How to do this? Here is how my WP_Query args are setup, but it doesn't return any results where it should:

array (
  'post_type' => 'property',
  'posts_per_page' => 10,
  'post_status' => 'publish',
  'tax_query' => 
  array (
    'relation' => 'OR',
    0 => 
    array (
      'taxonomy' => 'type',
      'field' => 'name',
      'terms' => 'Strip',
    ),
  ),
  'meta_query' => 
  array (
    'relation' => 'OR',
    0 => 
    array (
      'key' => 'city',
      'value' => 'Strip',
      'compare' => 'LIKE',
    ),
    1 => 
    array (
      'key' => 'state',
      'value' => 'Strip',
      'compare' => 'LIKE',
    ),
    2 => 
    array (
      'key' => 'salelease',
      'value' => 'Strip',
      'compare' => 'LIKE',
    ),
    3 => 
    array (
      'key' => 'zip',
      'value' => 'Strip',
      'compare' => 'LIKE',
    ),
  ),
)

Strip is the search term being sent to the value. I have a type with a name of Strip Centers, but I get empty result here. I think this is using AND to compare the Type with all of the meta_queries also. I just want any value of type OR any value of the meta_queries.

How to do this properly?

1

Looking at WP_Tax_Query and WP_Meta_Query used by Wp_Query, they return their respective where clauses with an AND relation, and no hook or $args to change that behavior.

Furthermore, the tax_query is computed before the rest of the main query so that it's included in the main query in term_taxonomy_id IN (...) form. Also the WP_Tax_Query doesn't accept a LIKE operator (see doc)

You can see the final query in WP_Query->request

SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID
FROM wp_posts  
LEFT 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 (1))
    AND (  ( wp_postmeta.meta_key = 'city' AND wp_postmeta.meta_value LIKE '%Strip%' ) 
        OR  ( wp_postmeta.meta_key = 'state' AND wp_postmeta.meta_value LIKE '%Strip%' ) 
        OR  ( wp_postmeta.meta_key = 'salelease' AND wp_postmeta.meta_value LIKE '%Strip%' ) 
        OR ( wp_postmeta.meta_key = 'zip' AND wp_postmeta.meta_value LIKE '%Strip%' ) ) 
    AND wp_posts.post_type = 'post'
    AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC LIMIT 0, 10

The solution for your issue is to do your own request with $wpdb to get all posts ID that match your search value and then convert it to WP_Post objects array.

function search_posts($value)
{
    global $wpdb;
    $value = '%'.$wpdb->esc_like($value).'%';
    $sql = $wpdb->prepare("SELECT p.ID
        FROM {$wpdb->prefix}posts p
        LEFT JOIN {$wpdb->prefix}postmeta m ON m.post_id = p.ID
        LEFT JOIN {$wpdb->prefix}term_relationships r ON r.object_id = p.ID
        LEFT JOIN {$wpdb->prefix}term_taxonomy tt ON tt.term_taxonomy_id = r.term_taxonomy_id AND tt.taxonomy = 'type'
        LEFT JOIN {$wpdb->prefix}terms t ON t.term_id = tt.term_id
        WHERE p.post_status = 'publish' AND p.post_type = 'property'
            AND ((m.meta_key = 'city' AND m.meta_value LIKE %s) 
                OR (m.meta_key = 'state' AND m.meta_value LIKE %s) 
                OR (m.meta_key = 'salelease' AND m.meta_value LIKE %s) 
                OR (m.meta_key = 'zip' AND m.meta_value LIKE %s)
                OR (t.name LIKE %s))
        GROUP BY p.ID", $value, $value, $value, $value, $value);
    $ids = $wpdb->get_col($sql);
    if (is_array($ids) && count($ids) > 0)
        return array_map('get_post', $ids);
    else
        return []; // or whatever you like
}
  • Thanks, that's the only way to do it right. The post_clauses filter is a total mess, and was trying with no luck to get taxonomy and meta working together somehow in there. could only really get the meta keys working nicely, but the taxonomy name is much more involved. – Solomon Closson Oct 28 '17 at 19:13
  • I suppose an INNER JOIN on the postmeta table is probably more optimized, and shouldn't hurt any, since all posts have postmeta. – Solomon Closson Oct 28 '17 at 19:16
  • INNER JOIN is better if you are SURE meta exists. If not the query will return nothing, even if a term matches the search string. – 86Dev Oct 29 '17 at 2:17

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.