WP_Query | Help me create a search term with an 'OR' relation?

Question

I have written a very elaborate script that will essentially allow the user to select from categories, tags and of course the search term. Then run these parameters through a WP_Query.

I can successfully achieve the results I want using the 'AND' relation, this works for when the user populates search parameters into all fields; search term, tags & categories. It will also work for leaving one of the three parameters blank, two of the parameters blank and will return all products when all parameters are left blank.

I am then trying to add in an option to use the relation 'OR'. Where it will allow them to still add in the three search parameters however the results should only have to apply to one of the parameters. This works if the user only selects from tags and categories, however it doesn't return the correct results if they use the search term parameter.

I believe this to be because the relation is only set to the tags and categories, I need this to be extended to the search term too.

Hopefully you can grasp what I am trying to achieve, in my snippet you can assume all variables populating are correct, I simply wish to ask for help adding the search term as a parameter with the relation 'OR'. Here is my current WP_Query:

$paged              = !empty($_POST['page'])                ? $_POST['page']                : 1;
$display_count      = !empty($_POST['display_count'])       ? $_POST['display_count']       : 9;
$direction          = !empty($_POST['direction'])           ? $_POST['direction']           : '';
$search_term        = !empty($_POST['search_term'])         ? $_POST['search_term']         : '';
$search_tags        = !empty($_POST['search_tags'])         ? $_POST['search_tags']         : '';
$search_categories  = !empty($_POST['search_categories'])   ? $_POST['search_categories']   : '';
$search_relation    = !empty($_POST['search_relation'])     ? $_POST['search_relation']     : 'AND';

$offset_modifier    = 0;
if      ($direction     === 'prev') :
    $offset_modifier        = $paged - 2;
elseif  ($direction     === 'next') :
    $offset_modifier        = $paged;
elseif  ($direction     === 'last') :
    $offset_modifier        = $paged - 1;
endif;
$offsetcalc = $offset_modifier * $display_count;

$args = array(
    'post_type'         => 'product',
    'post_status'       => 'publish',
    'orderby'           => 'menu_order',
    'order'             => 'ASC',
    'posts_per_page'    => $display_count,
    'page'              => $paged,
    'offset'            => $offsetcalc,
    'tax_query'         => array()
);

if ( $search_term != '' ) :
    $args['s'] = $search_term;
endif;

if ( $search_tags != '' ) :
    $args['tax_query']['product_tag'] = array(
        'taxonomy'          => 'product_tag',
        'field'             => 'slug',
        'terms'             => array($search_tags),
    );

    if (strpos($search_tags, ', ') !== false) {
        $args['tax_query']['product_tag']['relation'] = $search_relation;
    }
endif;

if ( $search_categories != '' ) :
    $args['tax_query']['product_cat'] = array(
        'taxonomy'          => 'product_cat',
        'field'             => 'slug',
        'terms'             => array($search_categories),
    );

    if (strpos($search_categories, ', ') !== false) {
        $args['tax_query']['product_cat']['relation'] = $search_relation;
    }
endif;

if ( $search_tags != '' && $search_categories != '' ) :
    $args['tax_query']['relation'] = $search_relation;
endif;

$the_query = new WP_Query( $args );

if ( $the_query->have_posts() ) :

So trying my best to adapt this to have the relation of 'OR' on the search term (note the "if($search_term != ''):" and the change to the "$args['tax_query']['relation'] = $search_relation;" IF statement):

$paged              = !empty($_POST['page'])                ? $_POST['page']                : 1;
$display_count      = !empty($_POST['display_count'])       ? $_POST['display_count']       : 9;
$direction          = !empty($_POST['direction'])           ? $_POST['direction']           : '';
$search_term        = !empty($_POST['search_term'])         ? $_POST['search_term']         : '';
$search_tags        = !empty($_POST['search_tags'])         ? $_POST['search_tags']         : '';
$search_categories  = !empty($_POST['search_categories'])   ? $_POST['search_categories']   : '';
$search_relation    = !empty($_POST['search_relation'])     ? $_POST['search_relation']     : 'AND';

$offset_modifier    = 0;
if      ($direction     === 'prev') :
    $offset_modifier        = $paged - 2;
elseif  ($direction     === 'next') :
    $offset_modifier        = $paged;
elseif  ($direction     === 'last') :
    $offset_modifier        = $paged - 1;
endif;
$offsetcalc = $offset_modifier * $display_count;

$args = array(
    'post_type'         => 'product',
    'post_status'       => 'publish',
    'orderby'           => 'menu_order',
    'order'             => 'ASC',
    'posts_per_page'    => $display_count,
    'page'              => $paged,
    'offset'            => $offsetcalc,
    'tax_query'         => array()
);

if ( $search_term != '' ) :
    $args['tax_query']['product_tit'] = array(
        'taxonomy'          => 'product_tit',
        'field'             => 'slug',
        'terms'             => $search_term,
    );
endif;

if ( $search_tags != '' ) :
    $args['tax_query']['product_tag'] = array(
        'taxonomy'          => 'product_tag',
        'field'             => 'slug',
        'terms'             => array($search_tags),
    );

    if (strpos($search_tags, ', ') !== false) {
        $args['tax_query']['product_tag']['relation'] = $search_relation;
    }
endif;

if ( $search_categories != '' ) :
    $args['tax_query']['product_cat'] = array(
        'taxonomy'          => 'product_cat',
        'field'             => 'slug',
        'terms'             => array($search_categories),
    );

    if (strpos($search_categories, ', ') !== false) {
        $args['tax_query']['product_cat']['relation'] = $search_relation;
    }
endif;

if ((isset($search_term) && isset($search_tags)) || (isset($search_term) && isset($search_categories)) || (isset($search_tags) && isset($search_categories))) :
    $args['tax_query']['relation'] = $search_relation;
endif;

$the_query = new WP_Query( $args );

if ( $the_query->have_posts() ) :

My changes do not allow for the search term parameter to work, I imagine I am using the wrong methodology or the wrong taxonomy name 'product_tit'...

Please help me create the search term as a parameter with the 'OR' relation and ensure it is narrowed down to the products title only

Can anyone help?

Really appreciative of contributors, Jason.

EDIT: Latest code that doesn't work with the 'OR' relation.

// PRODUCT PAGINATION AJAX FUNCTION
    function load_products_by_ajax_callback() {
        $paged              = !empty($_POST['page'])                ? $_POST['page']                : 1;
        $display_count      = !empty($_POST['display_count'])       ? $_POST['display_count']       : 9;
        $direction          = !empty($_POST['direction'])           ? $_POST['direction']           : '';
        $search_term        = !empty($_POST['search_term'])         ? $_POST['search_term']         : '';
        $search_tags        = !empty($_POST['search_tags'])         ? $_POST['search_tags']         : '';
        $search_categories  = !empty($_POST['search_categories'])   ? $_POST['search_categories']   : '';
        $search_relation    = !empty($_POST['search_relation'])     ? $_POST['search_relation']     : 'AND';

        $offset_modifier    = 0;
        if      ($direction     === 'prev') :
            $offset_modifier        = $paged - 2;
        elseif  ($direction     === 'next') :
            $offset_modifier        = $paged;
        elseif  ($direction     === 'last') :
            $offset_modifier        = $paged - 1;
        endif;
        $offsetcalc = $offset_modifier * $display_count;

        $args = array(
            'post_type'         => 'product',
            'post_status'       => 'publish',
            'orderby'           => 'menu_order',
            'order'             => 'ASC',
            'posts_per_page'    => $display_count,
            'page'              => $paged,
            'offset'            => $offsetcalc >= 1 ? $offsetcalc : '',
            'tax_query'         => array()
        );

        // Parse the search tags/categories list into an array.
        $tags               = strlen( $search_tags )            ? wp_parse_slug_list( $search_tags )        : '';
        $cats               = strlen( $search_categories )      ? wp_parse_slug_list( $search_categories )  : '';
        $has_search_term    = ( strlen( $search_term ) > 0 );
        $relation           = strtoupper( trim( $search_relation ) ); // normalize it into all caps

        // We don't use $args['s']. Instead, we write our own custom SQL for searching
        // in the post title only. We also don't use the tax_query parameter (anymore).
        // This is basically $args['tax_query'], except that we *don't* add it to $args.
        $tax_query = array();

        if ( ! empty( $search_tags ) ) {
            $tax_query[] = array(
                'taxonomy'  => 'product_tag',
                'terms'     => wp_parse_slug_list( $search_tags ),
                'field'     => 'slug',
                'operator'  => $search_relation,
            );
        }

        if ( ! empty( $search_categories ) ) {
            $tax_query[] = array(
                'taxonomy'  => 'product_cat',
                'terms'     => wp_parse_slug_list( $search_categories ),
                'field'     => 'slug',
                'operator'  => $search_relation,
            );
        }

        if ( ! empty( $tax_query ) ) { // if empty, don't set the relation
            $tax_query['relation'] = $search_relation;
        }

        $_filter = true; // a (private) var for the closure below
        add_filter( 'posts_clauses',
        function ( $clauses ) use ( &$_filter, $search_term, $tax_query ) {
            if ( ! $_filter ) {
                return $clauses;
            }

            global $wpdb;

            $search_where = '';
            if ( $search_term ) {
                $like  = '%' . $wpdb->esc_like( $search_term ) . '%';
                $search_where = $wpdb->prepare( "$wpdb->posts.post_title LIKE %s", $like );
            }

            if ( ! empty( $tax_query ) ) {
                $tax_query = new WP_Tax_Query( $tax_query );
                $is_and = ( 'AND' === $tax_query->relation );

                $tax_query = $tax_query->get_sql( $wpdb->posts, 'ID' );
                if ( empty( $tax_query['where'] ) ) { // just in case..
                    if ( $search_where ) {
                        $clauses['where'] .= " AND ( $search_where )";
                    }
                    return $clauses;
                }

                $clauses['join'] .= $tax_query['join'];
                if ( $is_and ) {
                    $clauses['where'] .= $search_where ?
                        " AND ( $search_where ) {$tax_query['where']}" :
                        $tax_query['where'];
                } else {
                    $where = preg_replace( '/^ *AND\b/', '', $tax_query['where'] );
                    $clauses['where'] .= $search_where ?
                        " AND ( ( $search_where ) OR{$where} )" :
                        $tax_query['where'];
                }
                $clauses['groupby'] = "$wpdb->posts.ID";
            } elseif ( $search_where ) {
                $clauses['where'] .= " AND ( $search_where )";
            }

            return $clauses;
        } );

        $the_query = new WP_Query( $args );
        $_filter = false;
    
        if ( $the_query->have_posts() ) :

            woocommerce_product_loop_start();

            echo '<div id="product-list" class="product-list">';

                while ( $the_query->have_posts() ) :

                    $the_query->the_post();

                    wc_get_template_part( 'content', 'productloop' );

                endwhile;

            echo '</div>';

            woocommerce_product_loop_end();

        else :

            echo '<div class="no-search-results">This search returned no results, please refine your parameters!</div>';

        endif;

        $product_loop_count = $the_query->found_posts;
        echo '<div class="product-list-count">';
            echo '<div class="product-loop-count-text">Total product count:&nbsp;</div><div id="product-loop-count">' . $product_loop_count . '</div>';
        echo '</div>';
        
        wp_die();
    }
    add_action('wp_ajax_load_products_by_ajax', 'load_products_by_ajax_callback');
    add_action('wp_ajax_nopriv_load_products_by_ajax', 'load_products_by_ajax_callback');
// END PRODUCT PAGINATION AJAX FUNCTION

Answer 1

(Revised on March 25 2020 UTC)

So in this revised answer, I'll just begin with the code:

1. Define the variables: (I intentionally included only the $args part)

   // define POSTed/submitted variables here like $paged, $display_count and $direction
   
   // define the offset/'direction' stuff here
   
   // then define your $args array
   $args = array(
       'post_type'      => 'product',
       'post_status'    => 'publish',
       'orderby'        => 'menu_order',
       'order'          => 'ASC',
       'posts_per_page' => $display_count,
       // You should use 'paged' and not 'page'.
       'paged'          => $paged,
       'offset'         => $offsetcalc >= 1 ? $offsetcalc : '',
       'tax_query'      => array(),
   );
   

2. And here's the main code, for the "search term OR":

   // This is basically $args['tax_query'], except that we *don't* add it to $args.
   $tax_query = array();
   
   if ( ! empty( $search_tags ) ) {
       $tax_query[] = array(
           'taxonomy' => 'product_tag',
           'terms'    => wp_parse_slug_list( $search_tags ),
           'field'    => 'slug',
       );
   }
   
   if ( ! empty( $search_categories ) ) {
       $tax_query[] = array(
           'taxonomy' => 'product_cat',
           'terms'    => wp_parse_slug_list( $search_categories ),
           'field'    => 'slug',
       );
   }
   
   if ( ! empty( $tax_query ) ) { // if empty, don't set the relation
       $tax_query['relation'] = $search_relation;
   }
   
   $_filter = true; // a (private) var for the closure below
   add_filter( 'posts_clauses',
   function ( $clauses ) use ( &$_filter, $search_term, $tax_query ) {
       if ( ! $_filter ) {
           return $clauses;
       }
   
       global $wpdb;
   
       $search_where = '';
       if ( $search_term ) {
           $like  = '%' . $wpdb->esc_like( $search_term ) . '%';
           $search_where = $wpdb->prepare( "$wpdb->posts.post_title LIKE %s", $like );
       }
   
       if ( ! empty( $tax_query ) ) {
           $tax_query = new WP_Tax_Query( $tax_query );
           $is_and = ( 'AND' === $tax_query->relation );
   
           $tax_query = $tax_query->get_sql( $wpdb->posts, 'ID' );
           if ( empty( $tax_query['where'] ) ) { // just in case..
               if ( $search_where ) {
                   $clauses['where'] .= " AND ( $search_where )";
               }
               return $clauses;
           }
   
           $clauses['join'] .= $tax_query['join'];
           if ( $is_and ) {
               $clauses['where'] .= $search_where ?
                                    " AND ( $search_where ) {$tax_query['where']}" :
                                    $tax_query['where'];
           } else {
               $where = preg_replace( '/^ *AND\b/', '', $tax_query['where'] );
               $clauses['where'] .= $search_where ?
                                    " AND ( ( $search_where ) OR{$where} )" :
                                    $tax_query['where'];
           }
           $clauses['groupby'] = "$wpdb->posts.ID";
       } elseif ( $search_where ) {
           $clauses['where'] .= " AND ( $search_where )";
       }
   
       return $clauses;
   } );
   
   $the_query = new WP_Query( $args );
   $_filter = false; // disable the filter, to avoid issues with other WP_Query calls
   

A bit of explanation (for code #2 above)

What you're looking for is a WHERE clause that looks something like:

(There are other conditions/variations, but this is the main one)

WHERE post_type = 'product' AND (
  ( post_title LIKE '%<search term>%' ) # Find posts that matched the search term,
  OR ( product_tag IN (<ids/slugs>) )   # ..or those that are in the specified tags
  OR ( product_cat IN (<ids/slugs>) )   # ..or categories.
)

So in that code #2 above, I'm using the posts_clauses hook to add that WHERE clause (and do other relevant stuff), but for generating the proper SQL clauses for the taxonomy (tag/category) queries, I'm using WP_Tax_Query (which is what WP_Query uses, btw).

Additional Notes

1. I'm using wp_parse_slug_list() to parse the (comma-separated) tags and categories into an array of slugs. And you should also be using it to make sure we have a valid array and because the function just works well. :)

2. Remember that if offset is 0, then in the $args, the offset should be set to '' (i.e. an empty string) instead of 0, to make sure the $paged works as expected.

3. Just a personal note... sorry, I forgot about using WP_Tax_Query before. ;)