I have elaborate search functionality on one of my sites, it is using a custom WP_Query that will allow for an ‘AND’ & ‘OR’ relation between the users selections. It will then generate the result and overwrite the existing products in the product list. This is all being done using AJAX and a function within function.php.

Can anyone identify why this query doesn’t work when both the category and the tag are selected by the user, but only while the RELATION is set to ‘AND’ before they then submit their search, requesting the filtered products?

This is the function, I require someone to help me doctor it so that the functionality works for when the user populates:

• Just the search term

• Just the category (can be multiple)

• Just the tag (can be multiple)

• The search term & category

• The search term & tag

• The tag & category

• The search term & category & tag

• They must then also have the ability to select which RELATION they would like to apply to the search. Therefore if the user selects the relation ‘AND’, the products selected must apply to all of the selections made, including if it has multiple of one criteria as can be found within the category and tag options the user chooses from. Leaving the ‘OR’ relation which would mean it must only apply to one of the selections in order for the product to make it into the outputted products from the query.

Here is the current function within my function.php file:

// 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,
        '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).
    $term_ids = array();

    if ( ! empty( $tags ) ) :
        $ids = get_terms( array(
            'taxonomy'  => 'product_tag',
            'slug'      => $tags,
            'fields'    => 'ids',
        ) );
        if ( $ids && ! is_wp_error( $ids ) ) :
            $term_ids = array_merge( $term_ids, $ids );
        endif;
    endif;

    if ( ! empty( $cats ) ) :
        $ids = get_terms( array(
            'taxonomy'  => 'product_cat',
            'slug'      => $cats,
            'fields'    => 'ids',
        ) );
        if ( $ids && ! is_wp_error( $ids ) ) :
            $term_ids = array_merge( $term_ids, $ids );
        endif;
    endif;

    $_filter = true; // this (private) variable is used with the closure below

    add_filter( 'posts_clauses',
    function ( $clauses ) use ( &$_filter, $search_term, $relation, $term_ids ) {
        if ( $_filter ) {
            global $wpdb;

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

            if ( ! empty( $term_ids ) ) {
                $tr         = uniqid( 'tr_' );
                $ids        = implode( ', ', array_map( 'intval', $term_ids ) );
                $relation   = ( 'AND' === $relation ? 'AND' : 'OR' );
                $where      = $where ? "($where) $relation " : '';

                $clauses['join']    .=  " LEFT JOIN {$wpdb->term_relationships} $tr ON ({$wpdb->posts}.ID = {$tr}.object_id)";
                $clauses['where']   .=  " AND ( {$where}{$tr}.term_taxonomy_id IN ($ids) )";
                $clauses['groupby'] =   "{$wpdb->posts}.ID";
            } elseif ( $where ) {
                $clauses['where']   .=  " AND ($where)";
            }
        }
        return $clauses;
    } );

    $the_query = new WP_Query( $args );

    // now disable filtering the posts query clauses, to avoid issues with other
    // WP_Query calls.
    $_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 PAGINATION AJAX FUNCTION

Things you can assume:

• The variables passed are correct, it will successfully run the search and return the desired products if each of the options are populated individually (the user only puts in the search term, a category, a tag).

• The ‘OR’ relation always works, if only one, two or all three or the options (search term, category, tag) are selected.

• The ‘AND’ relation always works, if only one or two of the the options (search term, category, tag) are selected, NOT FOR ALL THREE.

Can anyone figure out why this doesn’t work for the relation ‘AND’ and all three search parameters are chosen by the user?

Appreciate all contributors, Jason.