Hey!

Im using the workflow module in combination with the Views module. I've got a View where i see many nodes of a special type, and i want to order them on the time the workflow state changes.

When i do this, i see duplicate items. This is normal, because one node can have multiple wokflow states. When i tell Views to display only distinct items, i get the following warning:

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 'DISTINCT(node.nid), node_data_field_io.field_io_value AS node_data_field_io_f' at line 1 query: SELECT DISTINCT(node.nid) AS DISTINCT(node.nid), node_data_field_io.field_io_value AS node_data_field_io_field_io_value, node.type AS node_type, node.vid AS node_vid, node.title AS node_title, node_data_field_io.field_pk_bsprijsexbtw_value AS node_data_field_io_field_pk_bsprijsexbtw_value, node.created AS node_created, node_data_field_io.field_pk_bsleverdatum_value AS node_data_field_io_field_pk_bsleverdatum_value, users.name AS users_name, users.uid AS users_uid, workflow_node_history.stamp AS workflow_node_history_stamp FROM node node LEFT JOIN workflow_node workflow_node ON node.nid = workflow_node.nid LEFT JOIN content_type_personeelskosten node_data_field_io ON node.vid = node_data_field_io.vid INNER JOIN users users ON node.uid = users.uid LEFT JOIN workflow_node_history workflow_node_history ON node.nid = workflow_node_history.nid WHERE (node.type not in ('page', 'story')) AND (workflow_node.sid = 14) AND (node.status <> 0) ORDER BY workflow_node_history_stamp DESC in /var/www/inkoop/sites/all/modules/views/includes/view.inc on line 735.

Do u know a way to fix this?

thnx in advance!

greets, Frank

Comments

brettev’s picture

brettev commented

I'm having this same problem. I fixed it in phpmyadmin so it didn't crap out by changing the sql to

SELECT DISTINCT(node.nid) AS nid

from

SELECT DISTINCT(node.nid) AS DISTINCT(node.nid)

but then it still returned the multiple rows, because it's doing distinct on the node id, and then joining to the workflow's tables. i know this kind of complicates things, but is there a way to set unique WORKFLOW, not just unique node id (before joins)??

Brett

neochief’s picture

neochief commented
Status: Active » Closed (duplicate)

Here's a proper issue: #284392: db_rewrite_sql causing issues with DISTINCT Patch from there solved the issue for me.