WordPress.org

Codex

Plugin API/Filter Reference/posts join

In wp_query object, the wordpress db tables are queried. But not all db tables are queried everytime.

For example, if i am on blog index page, it will query only the posts db table to display the list of posts.

What if i want to display the only those posts that have specific meta value for a specific meta key?

Another example can be in search pages: say, what if we want to search only the posts that have some specific meta value for a given meta key?

obviously we will require to alter the where clause of the wp_query object to include meta key and meta value conditions, but the search results will have no value, if the postmeta db table is not included in the table list.

to include the db tables in the query, we use 'posts_join' filter.

an example can be seen below:

// Join for searching metadata
function AIOThemes_joinPOSTMETA_to_WPQuery($join) {
    global $wp_query, $wpdb;

    if (!empty($wp_query->query_vars['s'])) {
        $join .= "LEFT JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id ";
    }

    return $join;
}

add_filter('posts_join', 'AIOThemes_joinPOSTMETA_to_WPQuery');

and to make the specific search, we might use the code similar to the following:

function AIO_AlphabeticSearch_WhereString( $where, &$wp_query )
{
    global $wpdb;
    if(isset($_GET['aioAlphaSearchMode']) && $_GET['aioAlphaSearchMode'] == 1){

        $searchAlphabet = $_GET['s']; 

        $where .= ' AND ' . $wpdb->posts . '.post_title LIKE \''.$searchAlphabet.'%\' ';

        // use only if the post meta db table has been joined to the search tables using posts_join filter
        $where .= " AND ($wpdb->postmeta.meta_key = 'JDReview_CustomFields_ReivewOrNewsPostType' AND $wpdb->postmeta.meta_value = 'JDReview_PostType_ReviewPost') ";
        return $where;
    }
}

add_filter( 'posts_where', 'AIO_AlphabeticSearch_WhereString', 10, 2 );
This page is marked as incomplete. You can help Codex by expanding it.