Facet API, exclude option, and a field with multiple values Im getting headaches concerning this one ...

Ive got a field, say a text field nammed "MyField", which can have multiple values.

Ive created a feed that imports the data for this filed.

After the import, my content looks like that :

Content A
Myfield :
- Element 1
- Element 2

Contnent B
Myfield :
- Element 1

Content C
Myfield :
- Element 2
- Element 3

Now im trying to create a facet with an exclude option.
What I would like is when I choose an element, all content, having at least the choosen element as a value of the Myfield field, should be hidden.

So if I check "Element 1", I want the content A and B to be hidden.
And if I check "Element 3", only the content C would be hidden.

The fact is that this is not the way it works ...

If I check "Element 1", only the content B will be hidden, because Content A has another value "Element 2" for this field, so it is not retrieved during the query.



I found something ...

The file "sites/all/modules/contrib/search_api/contrib/search_api_facetapi/plugins/facetapi/query_type_term.inc" contains the "addFacetFilter" function. This is where the exclude cases are processed.

If the Exclude settings is checked, this function changes the query condition to "&lt;>" (instead of "=")

This is the query that is generated concerning an exclude facet :

SELECT COUNT(*) AS expression FROM (SELECT DISTINCT t.item_id AS item_id, :score AS score, 1 AS expression FROM search_api_db_default_node_index t LEFT OUTER JOIN search_api_db_default_node_index_field_produit_organisation_pa t_2 ON t.item_id = t_2.item_id LEFT OUTER JOIN search_api_db_default_node_index_field_derniere_liste_allergen t_3 ON t.item_id = t_3.item_id WHERE (( (t.created >= :db_condition_placeholder_0) )AND (t_2.value = :db_condition_placeholder_1) AND( (t_3.value &lt;> :db_condition_placeholder_2) )))

Retrieved results are wrong.

AND( (t_3.value &lt;> :db_condition_placeholder_2) is the default part added to handle the excluding setting.

Ive managed to modify the SQL Query so that the retrieved results are the good ones :

SELECT DISTINCT t.item_id AS item_id FROM search_api_db_default_node_index t LEFT OUTER JOIN search_api_db_default_node_index_field_produit_organisation_pa t_2 ON t.item_id=t_2.item_id LEFT OUTER JOIN search_api_db_default_node_index_field_derniere_liste_allergen t_3 ON t.item_id = t_3.item_id WHERE (( (t.created >= :db_condition_placeholder_0) )AND t_2.value = :db_condition_placeholder_1) AND NOT EXISTS (select t_4.item_id FROM search_api_db_default_node_index_field_derniere_liste_allergen t_4 WHERE t_4.value = :db_condition_placeholder_3 AND t_4.item_id=t_3.item_id))

Ive replaced the excluding condition with :

AND NOT EXISTS (select t_4.item_id FROM search_api_db_default_node_index_field_derniere_liste_allergen t_4 WHERE t_4.value = 'moutarde' AND t_4.item_id=t_3.item_id)

Now it returns only items that have no occurrence of the selected term in its values

Now I need to know how I can manage to get these correct results with views ... any idea ?
I did ask the same question in search_api issue queue and this problem needed 2 patchs to be commited.

One for Search_api_database_search : https://www.drupal.org/node/2361329

Another for Search_api : https://www.drupal.org/node/2371099

Hope it can help !