3

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 ?

4

You two JOIN elements both with the same table. When you need to join one table more then once, you need to make each join unique by giving the table alias. When WP make the whole query, it takes that into account, but you have add 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

  • 1
    Thank you Milan, that has worked perfectly and thank you for explaining the reason so I can understand why :). I have marked your response as the answer :) – Randomer11 Oct 18 '17 at 20:51

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.