Hello!
I am receiving an SQL error when I create a fusioned view. The error is 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 'DISTINCT(v4node.nid) AS v4node_nid, v4votingapi_cache.value AS v query: SELECT DISTINCT(node.nid), votingapi_cache.value AS votingapi_cache_value, DISTINCT(v4node.nid) AS v4node_nid, v4votingapi_cache.value AS v4v4votingapi_cache_value, v4node.title AS v4node_title, v4node.changed AS v4node_changed FROM node node LEFT JOIN votingapi_cache votingapi_cache ON node.nid = votingapi_cache.content_id AND votingapi_cache.content_type = 'node' LEFT JOIN nodefamily nodefamily ON node.nid = nodefamily.parent_nid LEFT JOIN node v4node ON nodefamily.child_nid = v4node.nid LEFT JOIN votingapi_cache v4votingapi_cache ON v4node.nid = v4votingapi_cache.content_id AND v4votingapi_cache.content_type = 'node' WHERE (votingapi_cache.function = 'average' OR votingapi_cache.function IS NULL) AND (node.type IN ('content_deal')) AND (votingapi_cache.func in F:\sites\test\includes\database.mysql.inc on line 121.

I am using drupal 4.7.5, views 1.4, fusion 1.0 and nodefamily 1.0
Thanks for your help!

Comments

fago’s picture

fago
Primary language German
Location Vienna
CreditAttribution: fago commented
Title: SQL error » views fusion has problems with fused views that use the distinct field

views fusion has troubles with views, that use the distinct parameter. Make sure that the fused view doesn't use the distinct field - but you can still set it for the primary view and it will affect the whole view.

but, there might be some custom fields that adds the distinct field to the view's query, but not as field. this fields can't also be used in the fused view!

chrisroditis’s picture

chrisroditis CreditAttribution: chrisroditis commented

Thanks a lot! That did it!

fago’s picture

fago
Primary language German
Location Vienna
CreditAttribution: fago commented
Title: views fusion has problems with fused views that use the distinct field » problems with fused views that use the distinct field

I've written a views patch, which would allow us to fix the problem:
http://drupal.org/node/119742

Hopefully it goes in

chrisroditis’s picture

chrisroditis CreditAttribution: chrisroditis commented

Great! I'll try it as soon as possible, thanks a lot!

fago’s picture

fago
Primary language German
Location Vienna
CreditAttribution: fago commented
Status: Active » Fixed

The patch has been applied to views :)
So I've fixed that in the latest views_fusion development snapshot. You'll also need the latest views snapshot.

Operations-1’s picture

Operations-1 CreditAttribution: Operations-1 commented
Version: 4.7.x-1.0 » 5.x-1.x-dev

was this path applied to the 5.x version too? i have the latest views_fusion for 5.x (march 6) and i get the same error when i try to use distinct... if not, do you have an ETA?

you wrote: "but, there might be some custom fields that adds the distinct field to the view's query, but not as field. this fields can't also be used in the fused view!"

could you explain this better?

Operations-1’s picture

Operations-1 CreditAttribution: Operations-1 commented
Status: Fixed » Active

forgot to reopen

fago’s picture

fago
Primary language German
Location Vienna
CreditAttribution: fago commented
Status: Active » Fixed

the quoted restriction is history. You have to use the latest views -dev snapshot too, and it will work. with 5.x too.

Operations-1’s picture

Operations-1 CreditAttribution: Operations-1 commented

i installed the most recent versions o views and views fusion, and i keep getting this error even if i dont use distinct...

Operations-1’s picture

Operations-1 CreditAttribution: Operations-1 commented
Status: Fixed » Active

forgot to reopen.... again... :)

fago’s picture

fago
Primary language German
Location Vienna
CreditAttribution: fago commented
Status: Active » Fixed

so then, it's no problem with distinct.
make sure that you 've run update.php, then edit each view that is part of the fusion and save it again. then try again.
Then:
if the problem persists please open another issue for your problem.

Anonymous’s picture

(not verified) CreditAttribution: commented
Status: Fixed » Closed (fixed)
deadman’s picture

deadman CreditAttribution: deadman commented
Status: Closed (fixed) » Active

I'm still having this problem using the distinct filter. I've disabled all other filters created by other modules from the both views and tried all combinations of child - parent, parent - child, and fusing the views in both directions but i always get the error.

I am using views 1.6beta5 and the latest dev of views fusion on drupal 5.1

fago’s picture

fago
Primary language German
Location Vienna
CreditAttribution: fago commented

I also ran again over this problem - but this time it was caused by node access modules.

If node access modules are installed, drupal does query rewriting so that only the accessible nodes are returned. These query rewriting seems to cause troubles, when there is already a DISTINCT in the query. I've to investigate this issue a bit more...

So, are you also using a node access module?

deadman’s picture

deadman CreditAttribution: deadman commented

Yeah, I'm using organic groups so I guess that'll be the problem.

mooffie’s picture

mooffie CreditAttribution: mooffie commented

drupal does query rewriting [...]
These query rewriting seems to cause troubles

Yes, I can confirm that. The problem is in Drupal's db_distinct_field() and I've just proposed a patch:

http://drupal.org/node/229831