SQL syntax error with token when using Flag module relationship in Views integration

Maybe it would be helpful if you could use the Devel module's query display feature to show the syntax of the failing query. If that's too hard, you could post what the code is for the export of the view.

I don't know what happened to this issue but I'm facing it, too. It happens as soon as I add the flag relationship to the view.

Here's the query error:

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 '***CURRENT_USER***) ) GROUP BY n.nid ASC HAVING (COUNT(*) >= 1) ORDER BY sco' at line 3 query: CREATE TEMPORARY TABLE temp_faceted_search_results_1 (nid int unsigned NOT NULL, PRIMARY KEY (nid)) Engine=HEAP SELECT n.nid AS nid, 5 * (3.26184506551 * SUM(node_search_index.score * node_search_total.count)) + 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), MAX(c.last_comment_timestamp)) - 1274974920) * 6.43e-8) AS score FROM node AS n INNER JOIN search_index AS node_search_index ON n.nid = node_search_index.sid INNER JOIN search_total AS node_search_total ON node_search_index.word = node_search_total.word LEFT JOIN node_comment_statistics AS c ON n.nid = c.nid WHERE ((n.status = 1) AND (n.type IN ('artwork')) AND ((node_search_index.word = 'airplane') AND node_search_index.type = 'node')) AND n.nid IN (SELECT node.nid AS nid FROM node node LEFT JOIN flag_content flag_content_node ON node.nid = flag_content_node.content_id AND (flag_content_node.fid = 1 AND flag_content_node.uid = ***CURRENT_USER***) ) GROUP BY n.nid ASC HAVING (COUNT(*) >= 1) ORDER BY score DESC in /var/www/sandbox/imagereadydev.partialflow.com/sites/all/modules/faceted_search/faceted_search.inc on line 1174.

And here's the view:

$view = new view;
$view->name = 'faceted_search_results';
$view->description = '';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('relationships', array(
'flag_content_rel' => array(
'label' => 'flag',
'required' => 0,
'flag' => 'bookmarks',
'user_scope' => 'current',
'id' => 'flag_content_rel',
'table' => 'node',
'field' => 'flag_content_rel',
'relationship' => 'none',
),
));
$handler->override_option('fields', array(
'title' => array(
'label' => 'Title',
'alter' => array(
'alter_text' => 0,
'text' => '',
'make_link' => 0,
'path' => '',
'link_class' => '',
'alt' => '',
'prefix' => '',
'suffix' => '',
'target' => '',
'help' => '',
'trim' => 0,
'max_length' => '',
'word_boundary' => 1,
'ellipsis' => 1,
'html' => 0,
'strip_tags' => 0,
),
'empty' => '',
'hide_empty' => 0,
'empty_zero' => 0,
'link_to_node' => 0,
'exclude' => 0,
'id' => 'title',
'table' => 'node',
'field' => 'title',
'relationship' => 'none',
),
));
$handler->override_option('access', array(
'type' => 'none',
));
$handler->override_option('cache', array(
'type' => 'none',
));
$handler->override_option('use_pager', '1');

Getting the same error here when adding flag relationship

I'm getting the same error. I'm assuming the '***CURRENT_USER***' bit ought to be replaced by the user id or something similar.

Okay, I've found a temporary fix but it's hackish and doesn't really fix the underlying problem. I'm not really a developer so I don't know why there is an error, but the ***CURRENT_USER*** bit seems to throw up an SQL Error. To fix it, I replaced

    db_query('CREATE TEMPORARY TABLE '. $this->_results_table .' (nid int unsigned NOT NULL, PRIMARY KEY (nid)) Engine=HEAP '. $query->query(), $query->args(), $this->_results_table);
    $this->_results_count = db_result(db_query('SELECT COUNT(*) FROM '. $this->_results_table));
    $this->_ready = TRUE;
  }

with

    global $user;
    $query2 = $query->query();
    $query2 = str_replace("***CURRENT_USER***", $user->uid, $query2);
    db_query('CREATE TEMPORARY TABLE '. $this->_results_table .' (nid int unsigned NOT NULL, PRIMARY KEY (nid)) Engine=HEAP '. $query2, $query->args(), $this->_results_table);
    $this->_results_count = db_result(db_query('SELECT COUNT(*) FROM '. $this->_results_table));
    $this->_ready = TRUE;

The ***CURRENT_USER*** is a token from Views - it should get replaced when the query is run in a normal view, but isn't here for some reason. I think that the underlying problem is probably that Faceted Search is hooking into the View process too early, before it has gotten replaced.

I'm getting the same issue.

I've tried the solution in #5, and that has removed the error message, but now I'm getting "Your search yielded no results". I'm not sure if this is a consequence of this bug or unrelated.

I'd abandoned Faceted Search for this project, but have come back to it again.

After implementing #5, which takes care of the ***CURRENT_USER*** issue, there's another problem that's cropped up.

I'm using Session API to allow Anonymous users to do stuff, and as a result get an error that includes this:

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 '***FLAG_CURRENT_USER_SID***) LEFT JOIN term_node term_node_value_0 ON node.vid ' at line 3 query: CREATE TEMPORARY TABLE temp_faceted_search_results_2 (nid int unsigned NOT NULL, PRIMARY KEY (nid)) Engine=HEAP SELECT n.nid AS nid, 5 * (2.34496700469 * SUM(node_search_index.score * node_search_total.count)) + 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), MAX(c.last_comment_timestamp)) - 1300258388) * 6.43e-8) AS score FROM node AS n INNER JOIN search_index AS node_search_index ON n.nid = node_search_index.sid INNER JOIN search_total AS node_search_total ON node_search_index.word = node_search_total.word LEFT JOIN node_comment_statistics AS c ON n.nid = c.nid WHERE ((n.status = 1) AND (n.type IN ('recruit')) AND ((node_search_index.word = 'abraham') AND node_search_index.type = 'node')) AND n.nid IN (SELECT node.nid AS nid FROM node node LEFT JOIN flag_content flag_content_node ON node.nid = flag_content_node.content_id AND (flag_content_node.fid = 1 AND flag_content_node.uid = 1 AND flag_content_node.sid = ***FLAG_CURRENT_USER_SID***) LEFT JOIN term_node term_node_value_0 ON node.vid = term_node_value_0.vid AND term_node_value_0.tid = 17 LEFT JOIN content_type_recruit node_data_field_first_name ON node.vid = node_data_field_first_name.vid LEFT JOIN flag_content flag_content ON node.nid = flag_content.content_id AND (flag_content.fid = 1 AND flag_content.uid = 1 AND flag_content.sid = ***FLAG_CURRENT_USER_SID***) LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid WHERE (node.type in ('recruit')) AND (term_node_value_0.tid = 17) ) GROUP BY n.nid ASC HAVING (COUNT(*) >= 1) ORDER BY score DESC in /var/www/dev/cgg/public/sites/all/modules/faceted_search/faceted_search.inc on line 1183.

The "***FLAG_CURRENT_USER_SID***" appears to be a reference to the Session ID.

#6 seems to be a very plausible explanation for the problem. Does anyone have experience with this module such that they could correct the problem?