Database error when restricting search to a custom post type

HI Mateusz,

When I restrict a search to a particular custom post type, e.g. http://example.com/?s=SEARCHSTRING&post_type=citation I get the database error Not unique table/alias: 'wp_postmeta' – and, unsurprisingly, no results.

Here’s the query responsible for the error:

SELECT DISTINCT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.*
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_postmeta
ON wp_posts.ID = wp_postmeta.post_id
INNER JOIN wp_postmeta AS a
ON (a.post_id = wp_posts.ID)
LEFT JOIN wp_postmeta AS b
ON (((b.meta_id = a.meta_id + @@auto_increment_increment))
AND ((b.meta_key LIKE CONCAT('\_', a.meta_key))))
LEFT JOIN wp_posts AS c
ON ((c.post_name = b.meta_value)
AND (c.post_type = 'acf-field')
AND ((c.post_content LIKE '%:"text"%')
OR (c.post_content LIKE '%:"textarea"%')
OR (c.post_content LIKE '%:"wysiwyg"%')))
WHERE 1=1
AND (((b.meta_id IS NOT NULL)
AND (c.ID IS NOT NULL)
AND (a.meta_value LIKE '%SEARCHSTRING%'))
OR ((wp_posts.post_title LIKE '%SEARCHSTRING%')
OR (wp_posts.post_content LIKE '%SEARCHSTRING%')
OR (wp_posts.post_excerpt LIKE '%SEARCHSTRING%')))
AND ( wp_postmeta.meta_key = 'year_published' )
AND wp_posts.post_type = 'citation'
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+0 DESC
LIMIT 0, 15

“ACF: Better Search” settings are on the defaults, but switching to “Lite Mode” and/or “Incorrect Mode” makes no difference, though the queries are slightly simpler.

MySQL 5.7.6
WordPress 5.5.1
ACF Pro 5.9.1
ACF: Better Search 3.5.3

EDIT: It seems the above SQL error happens when the results are ordered using a pretty standard technique, i.e. filtering on pre_get_posts and testing for is_post_type_archive() and modifying the query accordingly:

$query->set( 'meta_key', 'year_published' ); // The ACF Field ordered by.
$query->set( 'orderby', 'meta_value_num' );
$query->set( 'order', 'DESC' );

Removing the orderby filter solves the Not unique table/alias – however since the order is required, it looks like some kind of modification is required – either in this plugin or the recommended ACF order-by method.

Thoughts?

Cheers, Christian

• This topic was modified 5 years, 8 months ago by Christian Wach.