Database search returns no results when applying multiple filters on a multi-valued field

I can confirm that this is happening, and since this part of the Facets module isn't backend-specific (it just alters the Search API query object, which should be handled the same by all backends) this will even occur for other search queries with multiple filters on one field, and is a bug in the DB backend.
Thanks for spotting this, I'll fix it as soon as I can!

Same problem here.
This issue makes facets search totaly unusable as you could only select one facet.

I have tried to see why this is happeing, and to start with the query I found (with devel query log) is not working as it should do in mysql.

Pretend my table (search_api_db_ec_search_index_field_ec_conf_tags) looks like this:

SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM search_api_db_ec_search_index_search_api_language t INNER JOIN search_api_db_ec_search_index_type t_2 ON t.item_id = t_2.item_id INNER JOIN search_api_db_ec_search_index_field_ec_conf_tags t_3 ON t.item_id = t_3.item_id WHERE ( (t_2.value = 'ec_conf') AND (t_3.value = '24') AND (t_3.value = '50') )) subquery

When running this in PMA I get nothing.

If i change to

SELECT COUNT(*) FROM (SELECT 1 AS expression FROM search_api_db_ec_search_index_search_api_language t INNER JOIN search_api_db_ec_search_index_type t_2 ON t.item_id = t_2.item_id INNER JOIN search_api_db_ec_search_index_field_ec_conf_tags t_3 ON t.item_id = t_3.item_id WHERE ( (t_2.value = 'ec_conf') AND (t_3.value = '24') AND (t_3.value = '50') )) AS expression

Note: I have moved "AS expression" to the last line.

I get 0 back.
Which is ok, because thats that the query should return here.

The issue here is that the WHERE statment is build wrong I think.

AND (t_3.value = '24') AND (t_3.value = '50') will always return FALSE as there cant be two values on the same row.

I think we need to either do one more subquery or build a sql-view for the values.

Ah, you're right, that seems to be the issue here. This already helps a great deal, thanks.
Now I just need to find some time to see where this has to be changed …

subscribe

Initial patch to fix this problem. Let me know if it worked out!

This patch worked fine for me!

Can anyone else than me try this out and then put it > RTBC ?

/ Bobodrone

Patch seems to do it's job. Just some cosmetic things that needs to be fixed:

+++ b/contrib/search_api_db/service.incundefined
@@ -620,7 +620,7 @@ class SearchApiDbService extends SearchApiAbstractService {
     }
-
+    ¶

Trailing whitespace error here...

+++ b/contrib/search_api_db/service.incundefined
@@ -1046,12 +1046,16 @@ class SearchApiDbService extends SearchApiAbstractService {
   /**
    * Helper method for adding a field's table to a database query.
+   * @param $suffix suffix is used when a alias should have a certain suffix ¶

And here.

Wow, a working patch for a DB backend bug! I bow to you in admiration! ;)
Attached is an updated patch, with some minor code style fixes. Also, I guess we only have to do a new joins if the field can actually have multiple values, so this is now checked, too. On the other hand, I guess we always want to use a new join for creating facets—although, due to the query nesting, it actually (apparently) does so at the moment anyways. Well, at least this way we save a tiny bit of performance.

In any case, thank you very much for tackling this!

Oh, wow, and thanks for reviewing, bobodrone and fabsor! Please also check the attached patch, although it should be the same.

Patch in #8 worked great for me

Thanks :)

OK, great. Committed.

Patch in #8 worked great for me

Yes :)

Big up : more 1000 users ...