I considered reopening Wrong SQL query when submitting multiple values., but thought a new bug might be more beneficial.
I am using the Autocomple textfield element with the Node.Title and Node.Body fields selected for search.
With a delimited value as a space.
Under the submitted values section, I have selected:
Multiple field result combination: Disjunction (OR)
Multiple value result combination: Conjunction. (AND)
Issue:
When I type in a query with multiple values. "question test" for example.
Actual Result:
I am getting results which have either "question" or "test" in either the Body or the Title
When viewing the query, if I set the Multiple field result combination and Multiple value result combination respectively, I am getting the following SQL results:
OR OR
WHERE ((node.type IN ('%s','%s','%s') AND ((node.title LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%' OR node.title LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%')))) AND node.status = 1 GROUP BY nhier.group_id
OR AND
WHERE ((node.type IN ('%s','%s','%s') AND ((node.title LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%' OR node.title LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%')))) AND node.status = 1 GROUP BY nhier.group_id
AND AND
WHERE ((node.type IN ('%s','%s','%s') AND ((node.title LIKE '%%%s%%' AND node_revisions.body LIKE '%%%s%%' AND node.title LIKE '%%%s%%' AND node_revisions.body LIKE '%%%s%%')))) AND node.status = 1 GROUP BY nhier.group_id
Expected Result:
For the main scenerio I described above (OR AND), shouldn't I be expecting to see results in which either the Title or the Body has both "question" and "test" in the results. like:
(node.title LIKE '%%%s%%' AND node.title LIKE '%%%s%%') OR (node_revisions.body LIKE '%%%s%%' OR node_revisions.body LIKE '%%%s%%')
Comments
Comment #1
danielb commentedThe settings you are talking about are not supposed to change the behaviour of autocomplete suggestions, it is a design flaw that is allowing you to even change those from OR to AND. Those settings are meant for what happens when you submit the form.
I think we have a feature missing here, so I'll think about this a bit.
Comment #2
hobbsb commentedSorry for the late response. And please excuse me, as you might have noticed already, there was a typo in the last line of SQL. Corrected:
Expected Result:
For the main scenerio I described above (OR AND), shouldn't I be expecting to see results in which either the Title or the Body has both "question" and "test" in the results. like:
(node.title LIKE '%%%s%%' AND node.title LIKE '%%%s%%') OR (node_revisions.body LIKE '%%%s%%' AND node_revisions.body LIKE '%%%s%%')Just for clarification, I believe the code I gave was retrieved while submitting the search, not through the autocomplete suggestion itself. As you stated, these options shouldn't effect the Autocomplete, since they are under the "Submitted Values" section of the "Elements" Edit screen.
There definitely might be a missing feature; but as far as I can tell, the "Multiple value result combination" feature that is there does not work. Can you confirm this?
Comment #3
danielb commentedAre you sure those are from submitting the form? Do you have your results matching set to 'contains' or something?
Comment #4
danielb commentedI think i've replicated the problem
Comment #5
danielb commentedDamn this is a pretty big bug, major brainfart on my part when I designed this...
Comment #6
danielb commentedAlright I've made changes to this and I'm satisfied the queries are working as expected. I've also clarified the settings around this. I think for your example you will need to choose the 2nd option in the 'nesting order' setting.
Comment #7
danielb commentedComment #8
danielb commentedactually still need to sort out that ambiguity about how the results settings affect the autocomplete suggestions
Comment #9
danielb commentedI've left the autocomplete fixing to this issue #1198688: Wrong SQL when using multiple search fields
Let me know if there are any more troubles with submitted results.