Allow CCK to work with GROUP BY support in Views

I'm marking the following issue as duplicate: #616370: node.type is added to SELECT after being added as a filter

I think we need feedback and testing to make sure we do not break anything.

Just wanted to mention that I think we could commit if we can get feedback from testing this patch using different views, with different fields, including fields with multiple values, and also looking at how it affects field permissions as implemented by Field Permissions or CCK Private Fields (specially view own, and privacy related permissions). Note that these modules simply take advantage of features implemented by content_access() in CCK.

That way this feature could be included in the next CCK release, so we could be ready when Views 3 stable is released.

So this patch is for 6.x-2.x-dev. Is there one for 3.x-dev or i can apply this one?

Update: Nevermind, found files to update in handlers folder. Those are the same for 2 and 3 i suppose.

thanks for this effort, having the possibility to avoid node duplicities (and group fields in a single node) in view would be great, i think should be as a high priority

I have the same issue in views2 with multiple images /image cache

Any update on this. The patch doesn't work with 6.x-2.8 and View 3 dev. See http://drupal.org/node/396380#comment-3485756

Here's a reroll of the patch for CCK 6.x-2.8. I haven't done extensive testing but it applies cleanly. I didn't change anything from the original patch in the issue.

In response to #3 - the content_access() should work since it is explicitly declaring that $node is optional - and it works well with Field Permissions. CCK Private Fields doesn't respect that though, so to support it out of the box we would have to fetch the nid/vid of one of the nodes that the field is attached to and node_load() that node - perhaps not the best solution, but a working solution and only something needed for Views using grouping.

Should also be noted that the patches in this issue only fixes grouping on a CCK field - not the aggregation of a CCK field, eg. the counting, summing or similar. That is due to CCK being bit excessive in the amount of additional fields that are added in its hook_views_data() or due to Views 3.x currently not handling those fields correctly when eg. counting the field.

Two patches attached - the first is a draft to the aggregation and the second an extension of the original patch in this issue that tries to load a node and thus avoiding incompatabilities with CCK Private Fields.

I take back what I wrote in #8 about the patch working with Field Permissions - I confused Field Permissions with Content Permissions. Content Permissions works, but Field Permissions doesn't.

Just like CCK Private Fields the Field Permissions ignore that $node is optional as a workaround for Views. That means that the "view own" permission would give equal access as the "view" permission with this patch.

Same is true for Field Permissions Plus

Should issues be opened in those modules?

(Related fact: The CCK Field Privacy module still relies on hook_nodeapi() to enforce its access control - so it doesn't work with Views fields at all.)

Here's a straight port of the second patch from #8 to CCK 2.9. I'm referring to the extension of the original patch to enable GROUP BY support.

I have no time to try this out now, but I want to keep track of this issue so I can come back to it and do some testing.

Thanks to the help of dereine, I was able to test this feature.

The patch in #10 does definitly not work.

I used tha patch 1 in #8 on a clean cck 2.9 installation:
http://drupal.org/files/issues/695298_cck_views_group_by.patch

Additionally you have to download the latest views version (April 2011). This fixes an error if no field is selected for the grouping clause.

My testcase looks like this:

- New nodetype 'mission' with an integer cck single value field 'duration'. Each mission has a duration (days).

Now you create a new view, add the duration field, select groupby and also select "SUM". Be sure to NOT add any field like node id, that could distinguish the query and disable the grouping.

The result is one row with the sum of all duration days of all missions.

sub

subscribe

Still an issue, right? Posted this to http://drupal.org/node/1028230#comment-4568960 but it might be directly related to this issue?

Thx #8, solved my duplicating row problem. I applied both patches.
CCK 6x-2.9, View3 6x-3x-dev

Cheers,

I read and tried every post I could find but I'm still at a dead end.
I'm using
Drupal 6.22
Views 6.x-3.0-alpha3
CCK 6.x-2.9

I set up a Content Type (Entry) with several fields, among them one (Number) is an integer and another one (Type) is a varchar
I made a View with
Style: Table
Use Grouping: Yes
Fields: Number (SUM), Type
Filter: Type is "Entry"

I was hoping for a single row for each distinct Type with Number being the sum of all the rows but I still get all the rows distinct

In the query i can see there are node_type, nid, node_vid, node_created in the Group By statement and no matter what I do I always get them, what I'm doing wrong?

Try View3 6x-3x-dev yet?

If so, this patch might also have effect. I think I did apply it (but am not sure it's relevant.)
http://drupal.org/node/1073350

@newnewuser

It worked!
thanks bro, you saved my month :)

I tried everything above and found that it worked if I was only looking at a single node and grouping on one of its fields. However as soon as I set up a relationship I ended up with the extra fields (nid, vid, type) for the node that I was referencing in the relationship. I made a small module that removes any fields that were not explicitly added in the interface using a hook_query_alter. I fear that this is pretty hacky so I would appreciate anyone who can take a quick look at the sandbox and see if it works for them (and if it creates some bigger issue that I missed).

http://drupal.org/sandbox/kevinob11/1302688

Thanks!

@Kevinob11
Thanks for module. I posted an issue in the modules page (http://drupal.org/node/1337262).

Just ran into this. Attached is a git based patch for cck-6.x-3.x-dev based on merlin's original patch (without the first white space hunk). Fixes aggregation with grouping for me.

Tried #22 for cck-6.x-3.x-dev and views 6.x-3.x-dev and it did not work for me. Any Ideas?

The sandbox module (#20) as workaround worked for me. Thanks for providing.
It would be nice if this problem will be addressed by CCK developers, so the sandbox module won't be needed in future.