WordPress database error: [Not unique table/alias: 'wp_postmeta']

Question

Someone very kindly helped me out on another question, which has now lead to this problem of two functions causing a database error. From what I've read it could have something to do with LEFT JOIN and/or no wp_reset_query() or wp_reset_postdata(). The error I'm getting is :

WordPress database error: [Not unique table/alias: 'wp_postmeta']
SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID FROM wp_posts LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id WHERE 1=1 AND ( wp_term_relationships.term_taxonomy_id IN (567) ) AND ( wp_postmeta.meta_key = 'featured_listing' ) AND wp_posts.post_type = 'business' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 10

I've narrowed it down to two functions one being :

// Order posts for featured posts first in search and categories

function custom_special_sort( $query ) {

    // if is this the main query and is this post type of business
    if ( ($query->is_main_query() && is_post_type_archive('business') ) || (is_main_query() && is_tax ('location') ) ) {

        // order results by the meta_key 'featured_listing'
       $query->set( 'meta_key', 'featured_listing' );
        $query->set( 'orderby', 'featured_listing' );
        $query->set( 'order', 'DESC' );

    }
}
add_action( 'pre_get_posts', 'custom_special_sort' );

and the other being :

function cf_search_join( $join ) {
    global $wpdb;

    if ( is_search() ) {    
        $join .=' LEFT JOIN '.$wpdb->postmeta. ' ON '. $wpdb->posts . '.ID = ' . $wpdb->postmeta . '.post_id ';
    }

    return $join;
}
add_filter('posts_join', 'cf_search_join' );

+

function cf_search_where( $where ) {
    global $pagenow, $wpdb;

    if ( is_search() ) {
        $where = preg_replace(
            "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
            "(".$wpdb->posts.".post_title LIKE $1) OR (".$wpdb->postmeta.".meta_value LIKE $1)", $where );
    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );

+

function cf_search_distinct( $where ) {
    global $wpdb;

    if ( is_search() ) {
        return "DISTINCT";
    }

    return $where;
}
add_filter( 'posts_distinct', 'cf_search_distinct' );

The first function changes the order of posts in a search result, to order them with a certain post_meta above the rest.

The second function, add custom fields to the search query instead of wordpress just searching for post content and titles.

Any help in resolving the conflict would be much appreciated. If I remove one of the functions, the other one operates as it should but they just won't work together, from my reading i assume its because they are trying to edit the same query ?

Answer 1

You JOIN two elements both with the same table. When you need to join one table more than once, you need to make each join unique by giving the table alias. When WP makes the whole query, it takes that into account, but you have added your own code that doesn't do that.

So, first function:

function cf_search_join( $join ) {
    global $wpdb;

    if ( is_search() ) {    
        $join .=' LEFT JOIN '.$wpdb->postmeta. ' cfmeta ON '. $wpdb->posts . '.ID = cfmeta.post_id ';
    }

    return $join;
}
add_filter('posts_join', 'cf_search_join' );

This sets postmeta table alias 'cfmeta' and uses it in the ON.

And the second one:

function cf_search_where( $where ) {
    global $pagenow, $wpdb;

    if ( is_search() ) {
        $where = preg_replace(
            "/\(\s*".$wpdb->posts.".post_title\s+LIKE\s*(\'[^\']+\')\s*\)/",
            "(".$wpdb->posts.".post_title LIKE $1) OR (cfmeta.meta_value LIKE $1)", $where );
    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );

Using the 'cfmeta' instead of wp_postmeta.

Milan

Answer 2

If anyone is still looking at this issue and cannot fix it by the wonderful solution provided above, take a look that you don't have any display errors / error reporting code still active.

I was looking for a very long time for this solution and it seems like these options below can adjust the queries and cause errors.

Removing this code fixed the problem.

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);