WordPress search causing database error

Resolved kcmau
(@kcmau)

5 years, 1 month ago
Hide Posts seems to be breaking the WordPress search and throwing a database error.
- WordPress 5.6.1
- WordPress Hide Posts 0.5.3
- PHP 7.4.9
- MySQL 5.7.33
A search for “help me” produces:
```
WordPress database error: [Not unique table/alias: 'wp_postmeta']

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID 
FROM wp_posts LEFT JOIN wp_postmeta 
	ON (wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_whp_hide_on_search' ) 
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id 
WHERE 1=1 
	AND (((wp_posts.post_title LIKE '%help me%') 
		OR (wp_postmeta.meta_value LIKE '%help me%') 
		OR (wp_posts.post_excerpt LIKE '%help me%') 
		OR (wp_posts.post_content LIKE '%help me%'))) 
	AND (wp_posts.post_password = '') 
	AND ( wp_postmeta.post_id IS NULL ) 
	AND wp_posts.post_type IN ('post', 'page', 'attachment') 
	AND (wp_posts.post_status = 'publish')
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_title LIKE '%help me%' 
DESC, wp_posts.post_date DESC LIMIT 0, 12
```
(formatted this to make it a little more readable)

I can see the query has two left joins to the same table wp_postmeta without the use of table aliases, so MySQL is a little unhappy.

I’ve tried deactivating all other plugins and get the same result. I’ve also tried deactivating WordPress Hide Posts and my search results are returned correctly.

I only have the plugin enabled in my private Dev site at the moment, I can afford to have search not working in Prod but the same problem was occurring there as well.

Any suggestions what to try next? Any help appreciated!

Thanks

Viewing 4 replies - 1 through 4 (of 4 total)

Plugin Author MartinCV
(@martin7ba)

5 years, 1 month ago

Hello,

Thank you for reporting this.

I will review this tonight and get back to you.

Which theme do you use? Have you tried with another theme?

Best,
Martin

Thread Starter kcmau
(@kcmau)

5 years, 1 month ago

Hi Martin,

It’s a custom theme we had made and updated over the years.

Ok, I just tried TwentyTwenty and it disappears. It has to be something in our theme that is clashing with WHP right?

Doh, why didn’t I think to try another theme first!

I’ll leave the thread open for now and report back what I find.

Thank you! You have been most helpful.

Regards,
Pete
Thread Starter kcmau
(@kcmau)

5 years, 1 month ago
Hi Martin,

I’ve found the issue! We are using the Advanced Custom Fields plugin and I’ve found the code from Adam Balée in our theme’s function.php file that enables searching of custom fields without the need for another plugin.

I’ve fixed it using a table alias for the postmeta table in Adam’s code. Here’s the modified code:
```
function cf_search_join( $join ) {
    global $wpdb;

    if ( is_search() ) {    
        $join .=' LEFT JOIN '.$wpdb->postmeta. ' pm ON '. $wpdb->posts . '.ID = pm.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 (pm.meta_value LIKE $1)", $where );
    }

    return $where;
}
add_filter( 'posts_where', 'cf_search_where' );
```
Of course the cf_search_distinct function does not need any changes as it does not contain any references postmeta.

This gives me the advanced search capability and WHP is still functioning as expected regular posts and custom post types.

It maybe worth considering using table a table alias for postmeta in WHP to avoid any potential conflicts? I imagine we wouldn’t be the only ones using this code.

I hope this helps someone who may be having a similar issue.

Thanks again for setting me on the right path.

Kind Regards,
Pete
Plugin Author MartinCV
(@martin7ba)

5 years ago

Hi, sorry for super late response and I am glad you found a solution.

I have been rather busy on personal matter this past month, but I will review this code and see what updates can be done.

I will be releasing major update soon, so I might include this too.

Best,
Martin

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘WordPress search causing database error’ is closed to new replies.