1

I'm making a query to pull posts from my post query based on a user entry in a form. I want to keep the form simple so I'm hoping to only have to use one field. That field would say search part number or product title. It's an going to be an Ajax response. Here's the code I have right now:

function led_search_function(){

    $args = array(
              'post_type'   => 'al_product',
              'post_status' => 'publish',
        'posts_per_page' => 20,
    );
 
    // for taxonomies / categories
    if( isset( $_POST['categoryfilter'] ) && $_POST['categoryfilter'] )
        $args['tax_query'] = array(
            array(
                'taxonomy' => 'product_category',
                'field' => 'id',
                'terms' => $_POST['categoryfilter']
            )
        );
 
    // create $args['meta_query'] array if one of the following fields is filled
    if( isset( $_POST['part_number'] ) && $_POST['part_number'] )
        $args['meta_query'] = array( 'relation'=>'OR' ); // OR means that both below conditions of meta_query should be true

    
    if( isset( $_POST['part_number'] ) && $_POST['part_number'] ) {
        //checks for the part number
        $args['meta_query'][] = array(
            'key' => 'part_number',
            'value' => $_POST['part_number'],
            'compare'   => 'LIKE',
        );
        //checks if variations have part numbers to add to the array
        $args['meta_query'][] = array(
            'key' => 'product_variations_$_part_number',
            'value' => $_POST['part_number'],
            'compare'   => 'LIKE',
        );
       }
 
    $query = new WP_Query( $args );
 
    if( $query->have_posts() ) :
              echo '<h2>Your Search Results</h2><div class="productCategories grid">';
        while( $query->have_posts() ): $query->the_post(); ?>
                                    <div class="product_cat">
                            <a href="<?php the_permalink();?>">
                                <?php the_post_thumbnail('small');?>
                                <h2><?php the_title();?></h2>
                    <?php 
                                    $subtitle = get_field('subtitle');
                                    if ( $subtitle ) { 
                                        echo '<h3 class="entry-subtitle">'.$subtitle.'</h3>';
                                    }
                    ?>
                            </a>
                        </div>
    
                        <?php
        endwhile;
        echo '</div>';
        wp_reset_postdata();
    else :
        echo 'No lights found';
    endif;
 
    die();
}

It's a bit misleading right now because the field on the form is still called "part_number" even though it will search for everything.

I'm hoping to be able to add a 3rd part to the query that will search for the post title as well (or parts of it...so if the someone typed "sun" it would return products called "sunbeast" or "sun set light".

The 2nd catch is that if that the search term is NOT in the title but is still a part number I need it to return a the results still.

I see that i can use the "s" variable but that searches content too and is strict on title case. Is there a better method?

UPDATE Based On @sally-cj I've tried with this: (all in my functions.php) but it returns all products no matter what I input.

<?php
//enqueue product filter ajax js
add_action( 'wp_enqueue_scripts', 'product_finder_scripts' );
function product_finder_scripts() {
    if ( is_front_page() ) {
        wp_enqueue_script( 'ajax-filter-script', get_stylesheet_directory_uri() . '/js/ajax-filter.js', array('jquery'), false, true );
    } 
}

add_action('wp_ajax_myfilter', 'led_search_function'); 
add_action('wp_ajax_nopriv_myfilter', 'led_search_function');
 
function my_posts_where( $where ) {
    $where = str_replace("meta_key = 'product_variations_$", "meta_key LIKE 'product_variations_%", $where);
    return $where;
}
//add_filter('posts_where', 'my_posts_where');

function led_search_function(){

    $args = array(
        'post_type' => 'al_product',
        'post_status' => 'publish',
        'posts_per_page' => 20,
    );
 
    // for taxonomies / categories
    if( isset( $_POST['categoryfilter'] ) && $_POST['categoryfilter'] )
        $args['tax_query'] = array(
            array(
                'taxonomy' => 'product_category',
                'field' => 'id',
                'terms' => $_POST['categoryfilter']
            )
        );
 
    /**
    // create $args['meta_query'] array if one of the following fields is filled
    if( isset( $_POST['part_number'] ) && $_POST['part_number'] )
        $args['meta_query'] = array( 'relation'=>'OR' ); // OR means that both below conditions of meta_query should be true

    
    if( isset( $_POST['part_number'] ) && $_POST['part_number'] ) {
        //checks for the part number
        $args['meta_query'][] = array(
            'key' => 'part_number',
            'value' => $_POST['part_number'],
            'compare'   => 'LIKE',
        );
        //checks if variations have part numbers to add to the array
        $args['meta_query'][] = array(
            'key' => 'product_variations_$_part_number',
            'value' => $_POST['part_number'],
            'compare'   => 'LIKE',
        );
    } 
    **/

    add_filter( 'posts_clauses', 'my_posts_clauses', 10, 2 );
    $query = new WP_Query( $args );
    remove_filter( 'posts_clauses', 'my_posts_clauses', 10 );
    
    if( $query->have_posts() ) :
        echo '<h2>Your Search Results</h2><div class="productCategories grid">';
        while( $query->have_posts() ): $query->the_post(); ?>
            <div class="product_cat">
                <a href="<?php the_permalink();?>">
                    <?php the_post_thumbnail('small');?>
                    <h2><?php the_title();?></h2>
                    <?php 
                        $subtitle = get_field('subtitle');
                        if ( $subtitle ) { 
                            echo '<h3 class="entry-subtitle">'.$subtitle.'</h3>';
                        }
                    ?>
                </a>
            </div>
        <?php
        endwhile;
        echo '</div>';
        wp_reset_postdata();
    else :
        echo 'No lights found';
    endif;

    
    die();
}

// In the theme's functions.php file, or somewhere in your plugin:
function my_posts_clauses( $clauses, $query ) {
    $part_number = $_POST['part_number'] ?? '';

    if ( strlen( $part_number ) && ! is_admin() ) {
        global $wpdb;

        $part_number2 = '%' . $wpdb->esc_like( $part_number ) . '%';

        $clauses['join'] .= " INNER JOIN $wpdb->postmeta my_mt1 ON {$wpdb->posts}.ID = my_mt1.post_id";

        // Search in the metadata part_number.
        $where = $wpdb->prepare( "(my_mt1.meta_key = 'part_number'
            AND my_mt1.meta_value LIKE %s)", $part_number2 );

        // Search in the metadata product_variations_$_part_number.
        $where .= $wpdb->prepare( " OR (my_mt1.meta_key = 'product_variations_\$_part_number'
            AND my_mt1.meta_value LIKE %s)", $part_number2 );

        // Search in post title.
        $where .= $wpdb->prepare( " OR ({$wpdb->posts}.post_title LIKE %s)", $part_number2 );

        $clauses['where'] .= " AND ( $where )";
        $clauses['groupby'] = "{$wpdb->posts}.ID";
    }

    return $clauses;
}
2
  • Sometimes 2 queries is easiest. Perhaps do a custom search on title and then use post__in for the second query.
    – shanebp
    Mar 26 at 21:00
  • yeah, that's my first inclination too, but wanted to see if there was a "slicker" option.
    – rudtek
    Mar 26 at 22:28
1

If you want to do it via a single WP_Query call, then you can use the posts_clauses filter hook.

And the trick is, don't set the part_number and product_variations_$_part_number meta queries via the meta_query arg, but use the above hook instead, i.e. manually build the SQL clauses.

Working Example

Note: In your led_search_function() code, the above-mentioned meta queries should first be removed.

// In the theme's functions.php file, or somewhere in your plugin:
function my_posts_clauses( $clauses, $query ) {
    $part_number = $_POST['part_number'] ?? '';

    if ( strlen( $part_number ) && ! is_admin() ) {
        global $wpdb;

        $part_number2 = '%' . $wpdb->esc_like( $part_number ) . '%';

        $clauses['join'] .= " INNER JOIN $wpdb->postmeta my_mt1 ON {$wpdb->posts}.ID = my_mt1.post_id";

        // Search in the metadata part_number.
        $where = $wpdb->prepare( "(my_mt1.meta_key = 'part_number'
            AND my_mt1.meta_value LIKE %s)", $part_number2 );

        // Search in the metadata product_variations_$_part_number.
        $where .= $wpdb->prepare( " OR (my_mt1.meta_key = 'product_variations_\$_part_number'
            AND my_mt1.meta_value LIKE %s)", $part_number2 );

        // Search in post title.
        $where .= $wpdb->prepare( " OR ({$wpdb->posts}.post_title LIKE %s)", $part_number2 );

        $clauses['where'] .= " AND ( $where )";
        $clauses['groupby'] = "{$wpdb->posts}.ID";
    }

    return $clauses;
}

So add that function and then make the WP query like so (you could instead use a closure than adding/removing the filter like below, but that will be up to you on implementing it):

add_filter( 'posts_clauses', 'my_posts_clauses', 10, 2 );
$query = new WP_Query( $args );
remove_filter( 'posts_clauses', 'my_posts_clauses', 10 );
4
  • Thanks for your help with this. I don't think I quite understand though. Can you check if I did it correctly in my update?
    – rudtek
    Mar 29 at 22:48
  • Yes, you did it correctly. But I didn't notice you're doing the filter via the admin AJAX, which means is_admin() is true. So try removing the && ! is_admin() in the my_posts_clauses() function?
    – Sally CJ
    Mar 30 at 22:17
  • I wish I had seen your answer earlier. I don't want you to think this isn't important. It's returning a search but not fully. It seems the first clause (// Search in the metadata part_number.) but not the 2nd (// Search in the metadata product_variations_$_part_number.) I tried adding another field that was in my product variations and it didn't provide results in the search either. Its so close...any other good ideas for me?
    – rudtek
    Apr 13 at 5:10
  • Sorry for the delay and sorry if this sounded silly.. but are you sure the meta key is actually indeed product_variations_$_part_number (note the $ symbol)? Try var_dump( $query->request ); and see if the SQL is good? (You can paste it in your comment or add it to the question)
    – Sally CJ
    Apr 15 at 7:49

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.