No results when using complex tax queries

Can you check the MySQL query Relevanssi generates from that? You can get it with the relevanssi_query_filter filter hook. It’s possible Relevanssi is not getting the complex tax_query correct. Does a less complex tax_query work as expected?

Sure! It does work just fine with a simple tax queries. It only started struggling for me when I needed to work with this one specific AND case relationship.

Here is the output of that filter:

SELECT DISTINCT(relevanssi.doc), 1 AS term, 1 AS term_reverse,
		1 AS content, 1 AS title, 1 AS comment, 1 AS tag, 1 AS link, 1 AS
		author, 1 AS category, 1 AS excerpt, 1 AS taxonomy, 1 AS customfield,
		1 AS mysqlcolumn, 1 AS taxonomy_detail, 1 AS customfield_detail, 1 AS
		mysqlcolumn_detail, type, item, 1 AS tf
		FROM wp_relevanssi AS relevanssi 
		WHERE relevanssi.term = relevanssi.term  AND ( relevanssi.doc IN (
			SELECT DISTINCT(tr.object_id)
			FROM wp_term_relationships AS tr
			WHERE tr.term_taxonomy_id IN (17)
		) OR relevanssi.doc IN (
				SELECT ID FROM wp_posts WHERE 1=1
				AND (
					SELECT COUNT(1)
					FROM wp_term_relationships AS tr
					WHERE tr.term_taxonomy_id IN (51,22)
					AND tr.object_id = wp_posts.ID ) = 2
				) OR relevanssi.doc IN (
				SELECT ID FROM wp_posts WHERE 1=1
				AND (
					SELECT COUNT(1)
					FROM wp_term_relationships AS tr
					WHERE tr.term_taxonomy_id IN (12,11)
					AND tr.object_id = wp_posts.ID ) = 2
				)) AND ( relevanssi.doc IN (
			SELECT DISTINCT(tr.object_id)
			FROM wp_term_relationships AS tr
			WHERE tr.term_taxonomy_id IN (17)
		) OR relevanssi.doc IN (
				SELECT ID FROM wp_posts WHERE 1=1
				AND (
					SELECT COUNT(1)
					FROM wp_term_relationships AS tr
					WHERE tr.term_taxonomy_id IN (51,22)
					AND tr.object_id = wp_posts.ID ) = 2
				) OR relevanssi.doc IN (
				SELECT ID FROM wp_posts WHERE 1=1
				AND (
					SELECT COUNT(1)
					FROM wp_term_relationships AS tr
					WHERE tr.term_taxonomy_id IN (12,11)
					AND tr.object_id = wp_posts.ID ) = 2
				)) AND (
			relevanssi.doc IN (
				SELECT DISTINCT(posts.ID) FROM wp_posts AS posts
				WHERE posts.post_type IN ('job')
			) 
		) GROUP BY doc, item, type ORDER BY doc ASC LIMIT 500

Additional context – Results that are tagged with the first condition of having the market germany applied does return results. It’s only results for the second criteria that are not returning items.

Looking through the MySQL output, the term IDs do match up at least. I’m not sure about that ID FROM wp_posts WHERE 1=1 condition though.

That’s not the problem. The WHERE 1=1 just means it gets all posts, you don’t want any restrictions at that point.

Anyway, it looks like Relevanssi’s logic is wrong here. The logic in the MySQL query is this:

(term 17) OR ( (term 51 AND 22) OR (term 11 AND 12) OR term 17) OR ( (term 51 AND 22) OR (term 11 AND 12) ) AND post type “job”

When you want this:

(term 17) OR ( (term 51 OR 22) AND (term 11 OR 12) ) AND post type “job”

In this case, the simplest solution would be to remove the relevanssi parameter from the query – why does this need to go through Relevanssi? You don’t have a search term there; why not do this as a plain WP_Query?

Ah gotcha! That makes sense.

We do have a programmatic condition here that adds an s to the array when a search term is passed. I just didn’t include it here since I was testing just getting the conditionals to work out, otherwise I’d definitely just run through it WP_Query to keep it easy (since it worked just fine).

I’ll try to construct a test case that matches what you’re trying to do. Meanwhile, another approach for complex queries is to do a wider search and then filter out the results afterwards. It’s not as neat as doing a tax_query, of course.

That’s what I was thinking about doing next and was trying to figure out the best way to do it. I’ll work through some ideas on Monday and see what I can come up.

Thanks for taking a look at this! If you need any specific information from me, just let me know. Happy to help anyway I can.