If a search is done against a table that is accessed via a relationship, the SQL for complex searches does not seem to take the relationship into account when being generated.

The specific circumstances when this occurs for me are:

  • a view with users as the base table
  • a relationship to a node, in this case further user information set up with the content_profile module.
  • An exposed "Search Terms" filter using the relationship to the node.

Entering a search phrase containing a short word, for example "of elephant" results in a SQL error:

You have an error in your SQL syntax; ... near 'LIKE '% of %' AND .data LIKE '% eleph %') ...

This seems to be related to some changes made in #263556: Complex search queries broken which corrected other problems in complex search query handling. Adding the relationship to the add_table call fixed this for me and does not break the other non-relationship searches that I'm using. As noted in the earlier issue, views/search integration has its complexities so I'd appreciate comments from anyone who might understand this well enough to spot any possible glitches or side effects.

CommentFileSizeAuthor
#1 views_handler_filter_search_new.patch1.04 KBstevehuk
views_handler_filter_search_new.patch759 bytesstevehuk

Comments

stevehuk’s picture

stevehuk commented
StatusFileSize
new views_handler_filter_search_new.patch1.04 KB

Here's a revised patch since the first one didn't substitute table aliases in the join. An uncleared cache tricked me into to missing this on the first attempt.

This is where my knowledge of views becomes stretched so my approach may not be correct. The patch sets the aliases by doing a string replace on the extra string in the relevant join, minimicing the approach already taken for the search query. I looked at using the existing join handling, but this seems to expect that the extra clause has a field (where it sorts out the alias) and a value (which is treated as a literal). In this case, we need two fields as shown in the clause after the AND here:

... INNER JOIN search_dataset search_dataset ON search_index.sid = search_dataset.sid AND (search_index.type = search_dataset.type) ...

Is there any way to have a join extra array that can substitute two fields in the relevant clause?

mustanggb’s picture

mustanggb commented
Issue summary: View changes
Status: Active » Closed (won't fix)