Including Custom Meta with posts_where query

3 Views

I have been looking around to see how to do this but the solutions i’ve found don’t seem to be working for me.

I’m making an AJAX powered search for some wordpress custom post types. I have got pretty far and everything is working great. Except now I am stuck on how to include the custom fields of the custom post type in my search (as well as the content and title). I just want to add to the SQL statement doing the searching but i’m not very well versed at SQL.

Read More

I have tried

Here is what I have so far in my functions.php to query the database once the search term is sent by the AJAX call: 

function display_all_products() {

        global $term;
        $term = trim(strip_tags($_POST['s']));
            if(!empty($_POST['s']))
            {
                add_filter( 'posts_where' , 'product_posts_where' );
            }
            $args = array( 
                'posts_per_page' => 50,
                'paged' => 1, 
                'post_type' => 'product', 
                'order' => 'DESC',
            );

            $the_query = new WP_Query( $args ); 

            if($the_query->have_posts()) {
                while ( $the_query->have_posts() ) {
                    $the_query->the_post();

                    $pid = get_the_ID();
                    $refno = get_post_meta( $pid, 'refno', true );
                    $yr = get_post_meta( $pid, 'yr', true );
                    $pieces = get_post_meta( $pid, 'pieces', true );
                    $figures = get_post_meta( $pid, 'figures', true );
                    ?>
                    <div class="my_box3">
                        <h3><?php the_title(); ?></h3>
                        <div class="padd10"><?php the_post_thumbnail(); ?></div>
                        <div class="padd10">
                            <ul>
                                <li> <?php _e('Referance Number', 'AuctionTheme');?>: <?php  echo $refno; ?></li>
                                <li> <?php _e('Year', 'AuctionTheme'); ?>: <?php echo $yr; ?></li>
                                <li> <?php _e('Pieces', 'AuctionTheme'); ?>: <?php echo $pieces; ?></li>
                                <li> <?php _e('Figures', 'AuctionTheme'); ?>: <?php echo $figures; ?></li>
                            </ul>
                    </div>

                              <?php}
            }
            wp_reset_query();
        die();
}

add_action('wp_ajax_show_all_products', 'display_all_products');
add_action('wp_ajax_nopriv_show_all_products', 'display_all_products');

function product_posts_where( $where ) {

        global $wpdb, $term;
        $searchTerms = explode(' ', $term);

        $i = 1;
        $where .=" AND (";

        foreach ($searchTerms as $word) {
            if($i === 1) {
                $where .= " ({$wpdb->posts}.post_title LIKE '%$word%' OR {$wpdb->posts}.post_content LIKE '%$word%')";
            } else {
                $where .= " OR ({$wpdb->posts}.post_title LIKE '%$word%' OR {$wpdb->posts}.post_content LIKE '%$word%')";
            }
            $i++;
        }

        $where .=")";

    return $where;
}

Adding this to the product_posts_where function makes the whole thing fail and removes the data from the custom fields! 

$where .= " OR ($wpdb->postmeta.meta_key = 'refno' AND $wpdb->postmeta.meta_value = '%$word%')";
Post Views: 3

Related posts

Leave a Reply

1 comment

  1. Well I managed to solve this sort of…

    The answer was to use separate search input boxes for each custom field being searched.

    Here is my working solution if anyone ever stumbles across this.

    function display_all_products() {

    global $term;
    $term = trim(strip_tags($_POST['s']));
    $refnoterm = trim(strip_tags($_POST['ref']));
    $yrterm = trim(strip_tags($_POST['yr']));

    if(!empty($_POST['s']) || !empty($_POST['ref']) || !empty($_POST['yr'])) {

            if(!empty($_POST['s']))
            {
                add_filter( 'posts_where' , 'product_posts_where' );
            }

            if(!empty($_POST['ref']))
            {
                $refno_meta = array(
                    'key' => 'refno',
                    'value' => $refnoterm,
                    //'type' => 'numeric',
                    'compare' => 'LIKE'
                );
            }

            if(!empty($_POST['yr']))
            {
                $yr_meta = array(
                    'key' => 'yr',
                    'value' => $yrterm,
                    //'type' => 'numeric',
                    'compare' => 'LIKE'
                );
            }

        $meta_query = array('relation' => 'OR',);

        array_push($meta_query, $refno_meta);
        array_push($meta_query, $yr_meta);

            $args = array( 
                'posts_per_page' => 50,
                'paged' => 1, 
                'post_type' => 'product', 
                'order' => 'DESC',
                'meta_query' => $meta_query,
            );

            $the_query = new WP_Query( $args );

            remove_filter('posts_where', 'product_posts_where');


            if($the_query->have_posts()) {
                while ( $the_query->have_posts() ) {
                    $the_query->the_post();

                    $pid = get_the_ID();
                    $refno = get_post_meta( $pid, 'refno', true );
                    $yr = get_post_meta( $pid, 'yr', true );
                    $pieces = get_post_meta( $pid, 'pieces', true );
                    $figures = get_post_meta( $pid, 'figures', true );

                    $categories = get_the_terms( $pid, 'product_cat' );

                    ?>

                    <div class="my_box3">
                        <h3><?php the_title(); ?></h3>
                        <div class="padd10"><?php the_post_thumbnail(); ?></div>
                        <div class="padd10">
                            <ul>
                                <li> <?php _e('Referance Number', 'AuctionTheme');?>: <?php  echo $refno; ?></li>
                                <li> <?php _e('Year', 'AuctionTheme'); ?>: <?php echo $yr; ?></li>
                                <li> <?php _e('Pieces', 'AuctionTheme'); ?>: <?php echo $pieces; ?></li>
                                <li> <?php _e('Figures', 'AuctionTheme'); ?>: <?php echo $figures; ?></li>
                                <li> <?php _e('Category and Theme'); ?>:
                                    <?php
                                    foreach( $categories as $category) {
                                        echo $category->name . ', ';
                                    }
                                    ?>
                                </li>
                            </ul>
                        <label for="product">Select Product: <input type="radio" name="product" value="<?php the_ID(); ?>" /></label>
                    </div>

                    <?php   
                }
            } else {
                _e('There are no products that match your search, please try again.', 'AuctionTheme');
            }

            wp_reset_query();
            //wp_reset_postdata();

    } else {
        _e('You must enter something in at least one search box or select a product using the dropdown boxes and try again', 'AuctionTheme');
    }

    die();
}

add_action('wp_ajax_show_all_products', 'display_all_products');
add_action('wp_ajax_nopriv_show_all_products', 'display_all_products');

function product_posts_where( $where ) {

        global $wpdb, $term;
        $searchTerms = explode(' ', $term);

        $i = 1;
        $where .=" AND (";

        foreach ($searchTerms as $word) {
            if($i === 1) {
                $where .= " ({$wpdb->posts}.post_title LIKE '%$word%' OR {$wpdb->posts}.post_excerpt LIKE '%$word%')";
            } else {
                $where .= " OR ({$wpdb->posts}.post_title LIKE '%$word%' OR {$wpdb->posts}.post_excerpt LIKE '%$word%')";
            }
            $i++;
        }

        $where .=")";

    return $where;
}