Find all product that contain terms with %keywords% in WP_Query

Question

I want to use WP_Query to find all product terms entered in the input field. Here is my WP_Query code.

$paged = ( get_query_var( 'paged' ) ) ? get_query_var( 'paged' ) : 1;
$args = array(
    'post_type'         => 'product',
    'product_cat'       => 'construction',
    'posts_per_page'    => 12,
    'paged'             => $paged,
    'tax_query' => array(
        array(
            'taxonomy'  => 'pa_reference-p-no',
            'field'     => 'slug',
            'terms'     => $input_search_txt
        )
    );
);

$loop = new WP_Query( $args );

'$input_search_txt' is the text entered in the input field. I want to find all products that contain this text in 'pa_reference-p-no'. For example, if I search for '123', I want to find products with terms '12345' or '123456'.

But now I can only see the product with the term '123'.

I want someone to help me.

Answer 1

Unfortunately, tax_query does not have a compare match modifier like meta_query, allowing for wildcard searches with the 'LIKE' operator. So you need to have a different approach. I can think of 2 ways to solve this problem,

1) Use the global $wpdb object to search for all the terms that match your text search and then use those term ids to add an operator match modifier to your current query, [EDIT: added a debug line, make sure you enable WP_DEBUG]

    global $wpdb; 
    $term_ids = $wpdb->get_results( 
        "
        SELECT {$wpdb->terms}.term_id 
        FROM {$wpdb->terms}, {$wpdb->term_taxonomy}
        WHERE {$wpdb->terms}.term_id =  {$wpdb->term_taxonomy}.term_id
            AND {$wpdb->term_taxonomy}.taxonomy LIKE 'pa_reference-p-no'
            AND {$wpdb->terms}.slug LIKE '%{$input_search_txt}%'
        ",
        ARRAY_N
    );
    //you can debug your term id results at this point by printing them in your log file, make sure WP_DEBUG is set to true in your wp_config.php file.
    error_log('TERM IDS:'.print_r($term_ids,true)); //should be an array of term ids.
    $args = array(
        'post_type'         => 'product',
        'product_cat'       => 'construction',
        'posts_per_page'    => 12,
        'paged'             => $paged,
        'tax_query' => array(
            array(
                'taxonomy'  => 'pa_reference-p-no',
                'terms'     => $term_ids,
                'operator'  => 'IN'
            )
        );
    );

    $loop = new WP_Query( $args );

2) the second method would be to add a custom sql condition to your query using the query filter posts_where filter. So you would simplify your query to search for only the 'product' posts and then modify the taxonomy condition using,

        add_filter( 'posts_where' , 'posts_where', 10,2 );

        function posts_where( $where , $query) {

            if( 'product' == $query->query_vars['post_type'] && !$query->is_main_query() && !$query->is_admin() ) {
                global $wpdb;

                if ( isset( $_POST['input_search_txt'] ) && !empty( $_POST['input_search_txt'] ) ) {

                   $where .= 
                   " 
                     AND {$wpdb->terms}.term_id =  {$wpdb->term_taxonomy}.term_id
                     AND {$wpdb->term_taxonomy}.taxonomy LIKE 'pa_reference-p-no'
                     AND {$wpdb->terms}.slug LIKE '%{$_POST['input_search_txt'];}%'
                 ";
                }
            }
            return $where;
        }

Both of these solution should work, although I haven't tested them. The first one is easier to debug, and only target your current query. The second solution is more efficient as it makes 1 less hit on your DB, however keeping in mind that if you don't filter the $query in the initial condition, you can end up having other custom queries being affected and these can be very difficult to debug.

Let me know in the comments below if you have any problems understanding it.