How Do I Use WP_Query to Run This Database Query as Search Result?

Question

I have created a search box with a drop down box of 4 options to customize the search:

• A general default search, no filter
• A custom search according to the post title for a custom post type only.
• A custom search according to the post content for a custom post type only.
• A custom search according to the meta value attached to a post for a custom post type only.

I've built the search box to handle this. It looks like this in the functions.php file:

function r3_search_form( $form ) {
    $form = '<form role="search" method="get" id="top_nav_search" action="' . home_url( '/' ) . '" >
    <div>

        <label class="screen-reader-text" for="type">' . __('Type of Search:') . '</label>
        <select name="type" id="ic_search_type" >
            <option value="all">General Search |search full site contents|</option>
            <option value="name">Mineral Name |search the approved name for the mineral|</option>
            <option value="loc">Locality |search by mine name, district, state, or country|</option>
            <option value="detail">Description |search the description of the mineral|</option>
        </select>
        <label class="screen-reader-text" for="s">' . __('Search Form:') . '</label>
        <input type="text" value="' . get_search_query() . '" name="s" id="s" />
        <input type="submit" id="searchsubmit" value="'. esc_attr__('Search') .'" />
    </div>
    </form>';

    return $form;
}

add_filter( 'get_search_form', 'r3_search_form' );

Using that search box returns a URL with a query string at the end that looks like this: ?type=loc&s=Arizona . You'll notice that I've only added one additional parameter type from what a default search would return.

Here's what I'm trying do to. Depending on the search result, I need to alter the main query to behave something like one of the following MySQL queries:

1. SELECT * FROM wp_posts,wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_custom_meta_key' AND wp_postmeta.meta_value LIKE '%$search_term%' AND wp_posts.post_type = 'custom-post-type' ORDER BY wp_posts.post_date DESC

2. SELECT * FROM wp_posts WHERE wp_posts.post_title LIKE '%$search_term%' AND wp_posts.post_type = 'custom-post-type' ORDER BY wp_posts.post_date DESC

I've found this post over at WordPress forums from two years ago: http://wordpress.org/support/topic/query-posts-by-custom-fields-value

This comes pretty close to what I'm wanting to accomplish, but my question is if there is a way to do this within the WP_Query loop exclusively without using the WP database class ($wpdb).

I've set up a pre-get-post filter function with a switch according to the type parameter in the functions.php file to handle this request, but I am not able to get the query to run as described above using just WP_Query class. Can it be done? Thanks.

Answer 1

Well, perhaps the answer could not be found in the WP_Query class for what I was trying to accomplish. Below is the final code that I plugged in to my functions.php file. I ultimately ended up search the WP_Query Class found in the wp_includes/query.php file. What I discovered is that there are a number of filter access points in which one can jump in and modify the query. I found that there was posts_search filter that allowed me to modify the search part of the MySQL query that was being sent to the database. There was an even more inclusive posts_where filter that allowed me to modify more of the MySQL query.

Thanks to @Scribu over on Google Groups for his recommendation to do a var_dump on the incoming query to see if the filter I was using allowed me to access the part of the query that I wanted to modify.

I ultimately decided however to access the all powerful posts_request filter which gave me the full MySQL query string and allowed me to pull out the core of the query request and replace according to one of the three different queries that I was needing to perform. I trimmed and stored in variables the beginning and ending of the query so as to not kill the query's ability to be set dynamically.

Here is the search form:

function my_search_form( $form ) {
    (isset($_REQUEST['type']))? $ic_sType = $_REQUEST['type'] : $ic_sType = '';


    $ic_selectOpts = array(
        'all' =>'General Search [search full site contents]',
        'name'=>'Mineral Name [search the approved name for the mineral]',
        'detail'=>'Description [search the description of the mineral]',
        'loc'=>'Locality [search by mine name, district, state, or country]'
    );

    $form = '<form role="search" method="get" id="top_nav_search" action="' . home_url( '/' ) . '" >
    <div>


        <label class="screen-reader-text" for="type">' . __('Type of Search:') . '</label>

        <select name="type" id="my_search_type" >';
        foreach($my_selectOpts as $myso_key => $myso_val){
            $form .= "<option value='$myso_key'";
            if($myso_key === $my_sType) $form .= "selected='selected'";
            $form .= ">$myso_val</option>";
        }

        $form .= '</select>
        <label class="screen-reader-text" for="s">' . __('Search Form:') . '</label>
        <input type="text" value="' . get_search_query() . '" name="s" id="s" />
        <input type="submit" id="searchsubmit" value="'. esc_attr__('Search') .'" />
    </div>
    </form>';

    return $form;
}

add_filter( 'get_search_form', 'my_search_form' );

And here is the search results filter:

function my_search_results($query){

    if(isset($_REQUEST['type']) && isset($_REQUEST['s']) && ($_REQUEST['type'] !== 'all')){

        $myType = $_REQUEST['type'];
        $mySearch = $_REQUEST['s'];

        $qBegin = str_replace(strstr($query, "FROM" ), '', $query). ' FROM';

        //echo '<br>the qBegin string: '. $qBegin;

        $qEnd = strrchr($query, "ORDER BY" );
        //echo '<br>the qEnd string:' . $qEnd;

        switch ($myType){

            case 'name': //Title search

                $query = $qBegin. ' wp_posts WHERE 1=1 AND wp_posts.post_title LIKE "%'.$icSearch.'%" AND wp_posts.post_type = "mineral" AND (wp_posts.post_password = "") AND(wp_posts.post_status = "publish") '.$qEnd;

                break;

            case 'loc': //Location Search

                $query = $qBegin. ' wp_posts,wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND 1=1 AND wp_postmeta.meta_key = "_location_meta" AND wp_postmeta.meta_value LIKE "%'.$icSearch.'%" AND wp_posts.post_type = "mineral" AND (wp_posts.post_password = "") AND(wp_posts.post_status = "publish") '.$qEnd;

                break;

            case 'detail': //Details Search

                $query = $qBegin. ' wp_posts WHERE 1=1 AND wp_posts.post_content LIKE "%'.$icSearch.'%" AND wp_posts.post_type = "mineral" AND (wp_posts.post_password = "") AND(wp_posts.post_status = "publish") '.$qEnd;

                break;

            default: 

                break;
        }

    } 

    return $query;

}
add_filter( 'posts_request', 'my_search_results');

Hopefully this helps someone else.

Answer 2

you use action hook to format the wp_query before execution take a look at Action reference pre get post you need to put your custom field filter options in [meta_query] for more detail on that check out wp_query codex function documentation.

modified example from codex:

    function meta_add( &$query ) {
    if ( /*search result page */ ) {
        $query->set( 'meta_query', array(/*assoc of the filter */) );
    }
}
add_action( 'pre_get_posts', 'meta_add' );

hope that helps you out