stijnd

@mateuszgbiorczyk I’ve experienced the same issue’s and have been running the created query directly on my MySQL server and see the following issue(s)

This part in the SQL query is causing some issue’s (it’s not giving errors, but the results are blank:

(b.meta_id IS NOT NULL)
AND (c.ID IS NOT NULL)

So the b.meta_id is always NULL in my case & so is c.ID for some reason, however, the a.meta_value below does work & I found the following stackoverflow thread about using aliases inside the where-clause and that that could cause issue’s!

Stackoverflow Thread

Changing that part to the following:

(a.meta_id IS NOT NULL)
AND (wp_posts.ID IS NOT NULL)

will fix the issue for me, I have locally adjusted the code inside acf_better_search/app/Search/Where.php lines 60 & 61 and that seemed to fix the issue for me. According to the stackoverflow the other aliasses shouldn’t work, but somehow they do. I also see that you join the same table twice (post_meta) but my SQL-query skills aren’t that good, so I don’t fully understand why they are joined twice…?

The full query that was being constructed just to give some more context:

SELECT DISTINCT SQL_CALC_FOUND_ROWS  wp_posts.*
FROM wp_posts

LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_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 ( wp_term_relationships.term_taxonomy_id IN (7) )

AND (
	(
		(b.meta_id IS NOT NULL)
		AND (c.ID IS NOT NULL)
		AND (a.meta_value LIKE '%Microsoft%')
		AND (a.meta_value LIKE '%Azure%')
	)
	OR (
		(x
			(wp_posts.post_title LIKE '%Microsoft%')
			OR (wp_posts.post_content LIKE '%Microsoft%')
			OR (wp_posts.post_excerpt LIKE '%Microsoft%')
		)
		AND (
			(wp_posts.post_title LIKE '%Azure%')
			OR (wp_posts.post_content LIKE '%Azure%')
			OR (wp_posts.post_excerpt LIKE '%Azure%')
		)
	)
)

AND wp_posts.post_type = 'cpt_jobs'
AND ((wp_posts.post_status = 'publish'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date
DESC LIMIT 0, 9