By bernardwright on

I have a query to the DB for some database for a flexinode. It fails now and looking at the error log I sewe that the system is sticking in a DISTINCT in the wrong place.

Query in code

$query = "SELECT fn.nid, fn.serialized_data FROM {flexinode_data} fn INNER JOIN {term_node} tn ON fn.nid = tn.nid WHERE ...
$sql = db_rewrite_sql($query);
$result = db_query($sql);

In the error log it is

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 '(n.nid), fn.serialized_data FROM flexinode_data fn INNER JOIN term_node tn ON fn' at line 1 query: include SELECT fDISTINCT(n.nid), fn.serialized_data FROM flexinode_data fn INNER JOIN term_node tn ON fn.nid = tn.nid WHERE tid = 36 in /usr/local/www/data-test/includes/database.mysql.inc on line 120.

where I now have
SELECT fDISTINCT(n.nid)

instead of
SELECT DISTINCT(fn.nid)

Why does this happen?

thanks

Comments

heine’s picture

heine commented

From http://api.drupal.org/api/HEAD/function/db_rewrite_sql:

db_rewrite_sql($query, $primary_table = 'n', $primary_field = 'nid', $args = array())

So make it: db_rewrite_sql($query, 'fn');

--
The Manual | Troubleshooting FAQ | Tips for posting | How to report a security issue.

bernardwright’s picture

bernardwright commented

Thanks will give that a try