Hi
Just been working through a problem where I'm searching different content types, some of which do have a certain search field, and some don't.
The end result was I couldn't do it.
This was because of the use of INNER JOINs in the search query, if I included content types *with* the field then the ones without the field were automatically excluded by the INNER JOIN. When I eventually tracked it down and used a hook to change the join to a LEFT JOIN I was able to search both types of content.
I was wondering how we might get around this - or whether it's even worth it. I know for the majority of situations it's a good thing (keeps the size of searches down for a start).
Perhaps an advanced feature on the config page to force the use of LEFT if the configurer requires it?
Otherwise, see my re-opening of #1160164: hook_search_api_query_INDEX_NAME_alter()? with an exciting new suggestion...
| Comment | File | Size | Author |
|---|---|---|---|
| #9 | i1204966-9.patch | 394 bytes | jelle_s |
Comments
Comment #1
drunken monkeyThis should only occur when filtering by the property that some of the content types don't have, right?
What is your exact usage here?
Maybe generally changing joins to be LEFT JOINs would make sense. Or we could find some conditions under which it is necessary.
Comment #2
adaddinsaneYes, that is the point. I had two types of content - one of which just needed to be filtered by terms, the other filtered by terms and an additional field. To do both in one search I had to intercept the final db_query and change the INNER to LEFT on that condition.
I'm totally in favour of INNER, it constrains the results very nicely (and reduces processing time) - except when it gets in the way. Which is why I suggest it as an option.
Comment #3
drunken monkeySo how exactly did you build the Search API query? Using OR filters, this should basically be possible.
It might just be the case that the code in the DB backend for this has a bug (or several). But in that case, you should post a bug report, not a feature request.
Comment #4
adaddinsaneBasically like this:
OR
--condition(type, arrayofunindexedtypes, 'IN')
--AND
----condition(type, arrayofindexedtypes, 'IN')
----condition(indexfield, indexvalue)
What was happening was that the same table alias was being used for both 'type's which meant that the condition was effectively applied to both conditions, hence the need to change it to a LEFT rather than INNER join.
Yes I suppose that may be a bug - unfortunately I have no time to investigate whether something's a bug (unless it's totally obvious) because I have commercial deadlines to meet.
Comment #5
drunken monkeyOK, that's definitely a bug in the DB backend. And sadly not the only one …
I don't know why this should be solved by setting the join type to "LEFT", but if you say so, I guess I have to believe it. So maybe we should really just change this as a quick fix? When I finally get to revamping the whole condition part to actually work, this will probably be solved on the way, anyways.
Comment #6
drunken monkeyMoving this to the new project.
Comment #7
damien tournoud commentedIn addition, the current use of INNER join can cause duplicates and result in incorrect counts.
I think this is a release blocker for this module.
Comment #8
drunken monkeyCan you give specific examples when this happens, or even a failing test?
Comment #9
jelle_sI encountered the same bug as described in this issue (can not confirm #7). Attached is a patch that fixes the bug.
Comment #10
attiks commented#8: use case
Content type with 2 term fields, both optional
Node 1 has only term (A) in term field 1
Node 2 has only term (B) in term field 2
Both term fields are indexed
Inside a view based on the index, you expose both filters (OR) and search for A and B
None of the nodes will show, because of the inner join to both tables
Expected behavior: both nodes are shown
Comment #11
drunken monkeyI think this should be fixed with #2007872: Missing results when using OR filters. Otherwise, please re-open.