0

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
8
  • Try removing the if with that $args['tax_query']['relation'] and just use 'tax_query' => array( 'relation' => $search_relation ) in your $args.
    – Sally CJ
    Feb 25, 2020 at 11:14
  • I think I need to fix this snippet too... $args['tax_query']['product_tit'] = array('taxonomy' => 'product_tit', Feb 25, 2020 at 11:19
  • Well maybe, why did you add it there? Is product_tit a valid taxonomy? And do the $search_tags and $search_categories hold a single term slug or multiple slugs?
    – Sally CJ
    Feb 25, 2020 at 11:32
  • It is not valid, this is half of the question really c: The tags will be a comma seperated string so it could be multiple categories , multiple tags, or multiples of both. Feb 25, 2020 at 11:34
  • I don't have an answer now, but you should understand the differences between the relation and operator parameters; they shouldn't be both set to $search_relation. Instead, you could add another field like $terms_relation.
    – Sally CJ
    Feb 25, 2020 at 11:45

1 Answer 1

2

(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. ;)

18
  • I am already passing the tags and categories as a comma separated string? The values gathered are placed in by a wp function, so these are always going to be correct. Please can we adapt this to use the strings provided by the $_POST[variables] :s Feb 26, 2020 at 12:31
  • Have you already tried the code? What issue(s) you get with using wp_parse_slug_list()? And I am indeed using the $_POST variables/values; e.g. the wp_parse_slug_list( $search_tags ) - that $search_tags is the one as in $search_tags = !empty($_POST['search_tags']) ? $_POST['search_tags'] : '';. I intentionally didn't include the variable definitions in my code, but in your code, they should be included.
    – Sally CJ
    Feb 26, 2020 at 13:20
  • This is a massive step forwards one tiny step back you may be able to help me search through strictly the title of each product, I believe it is actually working now, but I was thrown off as it searches product content too so wasn't returning what I predicted to begin with, my apologies. Feb 26, 2020 at 13:33
  • 1
    In each of the tax queries, i.e. the $tax_query[] arrays, try setting the operator to the search relation - e.g. $tax_query[] = array( 'taxonomy' => 'product_tag', 'operator' => $search_relation, ... ); Which means, if the search relation is AND, whenever two or more categories and/or tags are selected, WordPress will search for posts that are in all of the selected categories/tags. If the relation is OR, then WordPress will only check any of the categories/tags.
    – Sally CJ
    Mar 26, 2020 at 0:19
  • 1
    This worked c: Cheers for revisiting! Apr 8, 2020 at 17:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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