Hi.
I use search_api with postgresql database.
I have fields on my content type holding multiple values.
When I turn on the option "Add facet for missing values" I get a PDO exception.
I dont know if this issue should be reported at facetapi or here (search_api).
Let me know if more information is needed to reproduce the issue.
Below an example:
PDOException: SQLSTATE[22P02]: Invalid text representation: 7 ERRO: sintaxe de entrada é inválida para integer: "!" LINE 4: WHERE (th.parent > '0') AND( (th.tid IN ('!', '33', '44', ... ^: SELECT th.tid AS tid, th.parent AS parent FROM {taxonomy_term_hierarchy} th WHERE (th.parent > :db_condition_placeholder_0) AND( (th.tid IN (:db_condition_placeholder_1, :db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5)) OR (th.parent IN (:db_condition_placeholder_6, :db_condition_placeholder_7, :db_condition_placeholder_8, :db_condition_placeholder_9, :db_condition_placeholder_10)) ); Array ( [:db_condition_placeholder_0] => 0 [:db_condition_placeholder_1] => ! [:db_condition_placeholder_2] => 33 [:db_condition_placeholder_3] => 44 [:db_condition_placeholder_4] => 184 [:db_condition_placeholder_5] => 204 [:db_condition_placeholder_6] => ! [:db_condition_placeholder_7] => 33 [:db_condition_placeholder_8] => 44 [:db_condition_placeholder_9] => 184 [:db_condition_placeholder_10] => 204 ) em facetapi_get_taxonomy_hierarchy() (linha 157 de /srv/www/drupal/sites/all/modules/facetapi/facetapi.callbacks.inc).
Regards,
Gilsberty
P.S.: tested in the last dev version of search_api.
| Comment | File | Size | Author |
|---|---|---|---|
| #6 | 2136019-6--missing_taxonomy_facets.patch | 1.49 KB | drunken monkey |
| #4 | 2136019-4--missing_taxonomy_facets.patch | 1.45 KB | drunken monkey |
| #3 | facet error.jpg | 103.61 KB | gilsbert |
Comments
Comment #1
drunken monkeyThe issue is probably at the right place here. I first thought that it might be in the database backend and even found a bug there (#2136409: Fix handling of NULL filters), but I don't think that's related (though you're welcome to try it out).
I guess the problem only occurs when clicking on that facet, right? Or also when it just gets displayed?
In the former case, the problem would seem to be that out Facet API integration passes on the value it uses internally to represent a missing value (i.e.,
!) unchanged to the Search API query, which doesn't treat this value specially in any way. Therefore, the database backend then tries to filter for a term ID of "!", and since the term ID is an integer that would lead to the error you describe.However, I haven't seen any problems in that regard, and the code seems also correct, so I'm unsure where the bug could be.
Are you maybe using some other extension modules that could influence the Facet API or the Search API's implementation of it? Specifically, if the query type plugin gets replaced that could well be the problem.
Comment #2
gilsbert commentedHi.
I tested the patch at #2136409 and it did not fixed this issue.
After the test I did revert the patch. Should I keep it applied?
The problem occurs when the facet is displayed. In fact nothing is displayed and I only get the PDOexception message (I did upload a screen capture).
I dont believe I'm using any other module that might change the query... below there is the list of modules directly involved:
- facetapi
- facetapi_collapisble (not in use on my test case)
- search_api
- search_api_db
- search_api_sorts (in use to allow me define a sort when there is no term at the fullsearch textbox and a different sort using the relevance in case there is a term)
I'm not an expert on Drupal as you might already noted (I'm working on it daily). I'm reasonable good on SQL and I have a good experience with postgresql.
The query using '!' as a value to compare with an integer field will always fail in postgresql and I agree with your conclusions.
I believe the problem is restrict to postgresql database but I dont have mysql to test on it. As far I can remember mysql is not strongly prototyped and is probably just ignoring the '!' in the list of values.
We could try to fix it at the source not sending the '!' as a value to be compared or at the destiny treating the value accordingly.
I dont know which way should be the best.
Regards,
Gilsberty
Comment #3
gilsbert commentedComment #4
drunken monkeyI have now spotted the root cause of the problem. The issue is that we're using Facet API's mechanism for creating the taxonomy term hierarchy for facets, but the Facet API has no support for the "none/missing" facet. It therefore passes in the "!" regardless, resulting in the exception. (You're right, MySQL just ignores it. Awful …)
The attached patch should fix this.
As long as you don't experience the associated problems with
NULLfilters, no. Unless someone complains, I'll commit that patch in a few days anyways.Comment #5
gilsbert commentedHi.
I'm sorry. I should have told you at the very begining that the field was a taxonomy.
I tested your patch and now the facet is displayed with a "none" option followed by the correct accounting of how many records doesn't have the value informed in the respective field.
But when I click on this "none" option I get the following PDO exception.
PDOException: SQLSTATE[42601]: Syntax error: 7 ERRO: erro de sintaxe em ou próximo a ")" LINE 4: WHERE (th.parent > '0') AND( (th.tid IN ()) OR (th.parent ... ^: SELECT th.tid AS tid, th.parent AS parent FROM {taxonomy_term_hierarchy} th WHERE (th.parent > :db_condition_placeholder_0) AND( (th.tid IN ()) OR (th.parent IN ()) ); Array ( [:db_condition_placeholder_0] => 0 ) em facetapi_get_taxonomy_hierarchy() (linha 157 de /srv/www/drupal/sites/all/modules/facetapi/facetapi.callbacks.inc).
There is an error at "(th.tid in ())" and "(th.parent IN ())".
I can't see the entire query so I'm not sure if there are others problems.
The explanation is: postgresql will not accept the operator "in" followed by an empty list of values.
Regards,
Gilsberty
Comment #6
drunken monkeyThanks for testing and spotting this new error!
The attached patch should fix this new problem (as well as the old one). Please test again!
Comment #7
gilsbert commentedHi.
I tested patch #6 and it is working! Cheers!
Is there a way to change the text displayed for the "none" facet?
Regards,
Gilsberty
Comment #8
drunken monkeyThanks for testing again! Good to hear it works.
Committed.
Yes. Use
hook_facetapi_facet_info_alter()to alter the facet in question and add a'missing label'option with the desired value. Without custom code, it's not possible (unless you override the translation of "none" for the whole site).