This problem exists both for the current dev-snapshot as well as the latest release. After enabling faceted search and faceted search views, I tried to create a view with faceted search argument, and from then on out, I get the following MySQL error message displayed:

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''') AND (. IN ('')) AND (term_node.tid IN ('7','5','6','4','2','3','1')) ) GROU' at line 1 query: SELECT COUNT(DISTINCT(n.nid)) AS count, term_data.tid AS term_data_tid, term_data.name AS term_data_name FROM node AS n INNER JOIN term_node AS term_node ON n.nid = term_node.nid INNER JOIN term_data AS term_data ON term_node.tid = term_data.tid WHERE ((n.status = 1) AND (n.type IN ('wrecipe')) AND (term_data.vid = 1) AND (n.type IN ('wrecipe'))) AND n.nid IN (SELECT DISTINCT(node.nid) FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_hierarchy term_hierarchy ON term_node.tid = term_hierarchy.tid LEFT JOIN content_field_picture node_data_field_picture ON node.vid = node_data_field_picture.vid LEFT JOIN votingapi_vote votingapi_vote_updownvote_points ON node.nid = votingapi_vote_updownvote_points.content_id AND votingapi_vote_updownvote_points.content_type = 'node' AND votingapi_vote_updownvote_points.value_type = 'points' AND votingapi_vote_updownvote_points.tag = 'updownvote' LEFT JOIN node_counter node_counter ON node.nid = node_counter.nid INNER JOIN users users ON node.uid = users.uid WHERE (. '') AND (. IN ('')) AND (term_node.tid IN ('7','5','6','4','2','3','1')) ) GROUP BY term_data_tid ASC ORDER BY count DESC, term_data.weight ASC, term_data.name ASC LIMIT 0, 11 in /var/www/localhost/htdocs/version5/includes/database.mysql.inc on line 172.

Basically, the problem is the

WHERE (. '') AND (. IN (''))

bit, but I have no idea how that was created or where that comes from. It's definitely related to faceted search views, though (I've tried disabling / re-enabling this and other modules and can re-create the error message only by enabling and using faceted search views).

Any idea what the issue could be or how to fix it?

My PHP Version is:
[5.2.4_p20070914-pl2-gentoo]

Not sure if the information provided here is sufficient, if there is anything else that I could provide to help solve this, please let me know.

Thanks!
kmh

CommentFileSizeAuthor
#9 faceted_search_views_subquery.patch1.43 KBmh86

Comments

karlmoritz’s picture

karlmoritz commented

Apologies, I should add that I'm using the current dev-snapshot of views (5.x). This problem does not exist with the latest official views release, but unfortunately using that version is not an option due to it's issues with search ('b...') searching doesn't work etc. ..

karlmoritz’s picture

karlmoritz commented
Title: Faceted Search: Environment ID breaks MySQL Query » Views (patched or dev) breaks Faceted Search Views

http://drupal.org/node/165611#comment-590536

The above link is to an official patch (committed to dev by now), which solves a number of issues with views, but unfortunately seems to be responsible for breaking the faceted search views module. So basically:

views 5.x-1.6 + faceted search views: works
views 5.x-1.6 + (important) patch + faceted search views: broken
views 5.x-dev + faceted search views: broken

Will investigate further what the problem could be, but any help from people more involved in the module would be highly appreciated :)

karlmoritz’s picture

karlmoritz commented
Status: Active » Needs work

OK, figured this out a little:

The problem is that faceted search somehow adds code to the query (%s ....) and args that aren't replaced by views anymore. This used to be taken care of by the _views_replace_args() function, which has been removed by the patch, and which slightly seems to have changed the functionality of views_build_view().

The below code fixes the problem, but I suppose it doesn't exactly address the underlying issue (namely correct integration with views). By adding an additional function to replace the %s's (and %d's, should there be any?) with their according arguments, the changed views functionality is countered.

By using this manual preg_replace rather than the preg_replace_callback originally used in _views_replace_args(), the problem of the ('b...') search etc is also avoided.

Anyway, here it goes:

In faceted_search_views.module add

function faceted_search_views_replace_args($clause, $args) {
  // cut & pasted directly from db_query.
  if (isset($args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
    $args = $args[0];
  }
  foreach($args as $arg)
    $clause = preg_replace("/\%s|\%d/",$arg,$clause,1);
  return $clause;
}

and alter the function "query_alter()":

  function query_alter(&$query, $search) {
    $view = $this->get_view();
    $queries = views_build_view('queries', $view, array($search->get_env_id()));
    
    $queries['query'] = faceted_search_views_replace_args($queries['query'],$queries['args']);
    $queries['countquery'] = faceted_search_views_replace_args($queries['countquery'],$queries['args']);

....

kmh

amccann’s picture

amccann commented

Thanks for your work on this. I just ran into it today.

I'm still getting these error messages after patching views with mw_83.patch per the thread and adding your additions to faceted_search_views.module. I have the latest faceted search module code and I'm running on CentOS 5.

It looks to me like the error messages haven't changed which leads me to believe I haven't set it up correctly.

Any thoughts appreciated.

user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) FROM node node WHERE (node.status = '1') AND (node.type IN ('ask'))' at line 1 query: SELECT count(DISTINCT()) FROM node node WHERE (node.status = '1') AND (node.type IN ('ask')) in /var/www/vhosts/empowher.com/httpdocs/includes/database.mysql.inc on line 172.
user warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' node_created_created ORDER BY node_created_created DESC LIMIT 0, 10' at line 1 query: SELECT count( DISTINCT(node.nid)) AS num_nodes, node.created AS node_created_created FROM node node WHERE (node.status = '1') AND (node.type IN ('ask')) GROUP BY , node_created_created ORDER BY node_created_created DESC LIMIT 0, 10 in /var/www/vhosts/empowher.com/httpdocs/includes/database.mysql.inc on line 172.

Any thoughts as to what I might be doing wrong.

amccann’s picture

amccann commented

I will pay to get this fixed if it helps.

Thanks!

karlmoritz’s picture

karlmoritz commented

Hey there, sorry for the late reply - this had nothing to do with money issues, but rather with a slight lack of time on my side ;) Your SQL query seems a bit strange, could you post the whole query that throws the error message? The bit

FROM node node WHERE (node.status = '1') AND (node.type IN ('ask'))

looks a bit weird. "node node" and then the additional closing parentheses ...

If you could post $queries['query'] before and after running it through the new function, that might give a little more useful info. I.e. in query_alter(), add print_r($queries['query']); before and after the call of the new function from my 'patch'...

Won't be online for two weeks from Sunday onwards, so my reply might take a while, but happy to try helping you out, once back online.

amccann’s picture

amccann commented

No problem - I didn't mean to imply anything negative ;-) I'm totally appreciative of the work you're doing on this.

I think it might be related to this issue and will try testing your above fix again this evening...
http://drupal.org/node/245180

Thanks
Alan

amccann’s picture

amccann commented

Per your suggestion (slightly modified), I posted to the watchdog instead

watchdog('faceted_search_before', $queries['query']);

watchdog('faceted_search_after', $queries['query']);

It never gets to that point when I look at the view with the environment variable tied to the faceted search environment

The error gets thrown first.

*********

However, let me clarify where it isn't working a bit better:

base path is: facetedtest/search
start page is: facetedtest
view url is: facetedtest

If I go to facetedtest/search and then conduct a guided search, all works fine.

I used to be able to look at the view at facetedtest (and have the associated faceted search block populated on that page) .... this is the page the throws the error. It also happens if I "view" the view from the edit view page.

What this give is an experience of is that when you come to the start page, it shows all of the content applicable sorted by the view and then you can drill down to select relevant content out of the whole list. I have simulated this at http://empowher.com/ask and http://empowher.com/share where I have settings like above but the Environment isn't set in the view. It was working before and then stopped when I either updated faceted search or views. I returned to the previous version of views.

This may not be what you expected but it is a great way to start the faceted search.

Do the view filtering settings matter?

It looks like the sql code should say {node} node rather than node node.

Thanks for your continued help on this.

mh86’s picture

mh86 commented
Status: Needs work » Needs review
StatusFileSize
new faceted_search_views_subquery.patch1.43 KB

I took a closer look at this problem, because I want to use the faceted search module in combination with views too.
The problem was, that the $query->add_subquery in query_alter was called without the query arguments. Second the add_subquery function didn't work proper with arguments in array structure. I fixed that too.

I attached the patch fixing the problems. Please take a look at it and test it :)

karlmoritz’s picture

karlmoritz commented

Works for me. Far more elegant than my hack. Thanks :)

David Lesieur’s picture

David Lesieur commented
Status: Needs review » Fixed

Thanks for the patch!

I have committed it with slight changes:

  • Used operator && instead of 'and'.
  • Moved the comment to its own line (for readability) and terminate it with a period.
David Lesieur’s picture

David Lesieur commented
Status: Fixed » Needs work

I had to revert the patch because it breaks with Views 1.6. Searches did not return the expected results when using multiple keywords.

mh86’s picture

mh86 commented

I'm using the 1.6 version of views on my site too, but don't have any problems with the patch. can you explain me the causing problems in more detail?

whereisian’s picture

whereisian commented

Hello, I just wanted to report that I applied the patch to my D5.7 site with the Views 5.x-1.x-dev (2008-May-11) and Faceted Search 5.x-1.0-beta3 and it solved this issue.

David Lesieur’s picture

David Lesieur commented
Version: 6.x-1.x-dev » 5.x-1.x-dev

Not an issue with Drupal 6/Views 2.