Enhance Views to support proper GROUP BY queries

This might be the same issue as posted here: #381890: Does the views have facility of sum up in serialize way.

Try using the views_calc module and see if that works.

It is my understanding that the views_calc module basically adds a row at the bottom of the view to give you a SUM or COUNT. What I want to do is create a field in a view where for every row, the value is calculated by executing a sub-view, and performing SUM or COUNT on that.

Yes, you're right - views_calc calculates on columns, now a row. If you'd like a field which counts the value of other fields within a particular node, take a look at the "computed field" module - you can use it to add up other values entered into that node.

Thanks for the suggestion. Computed field could work, but it's not ideal. If I want the total sales for each department, I would have to add a "total_sales" field to the department content type. That means that every time a department is viewed for any purpose, this figure would have to recalculate. If I only recalculate the field when a department is edited, I won't have accurate results (departments are rarely edited).

The other issue is what time period to use. Should the field calculate for the year? for the month? What if the person using the view wants to look at a different time period. For this reason, I think the calculation should be done when the view is constructed.

I appreciate your help, but I'm pretty sure the functionality I need doesn't exist. I'd like to build it and contribute it back. All I need is some guidance from those who know views inside and out (merlinofchaos?). Maybe you could comment on my suggested approach? Maybe it isn't possible for some reason?

Thank you very much!

> That means that every time a department is viewed for any purpose, this figure would have to recalculate.
Actually, only when the page is edited will the field recalculate. Drupal will cache your department node once it's saved, so that even if it's viewed 1000 times after that, your computed field won't have to run again (assuming that you set it to save its result to the database).

You do have a point about time periods, though - that might be useful. Although why don't you just get a view to return all employees from a department, filter by pay period, then use views_calc to total up those amounts?

Although...if your view returns a display by month in a table view, you would need to have a sub-total at the end of each row to show the amount an employee has earned during that time?

Something like:
Jan | Feb | Mar | Total
100 | 200 | 150 | 450

Maybe there's a way to extend views_calc to get it to calculate by row, rather than by column?

I caught merlinofchaos on IRC yesterday, and I think we are going to work together to implement the feature as a part of views. Thanks again for your help and suggestions, burningdog!

That's excellent - good luck!

@merlinofchaos -- I was just wondering when we might be able to get started on this. I was hoping to work on it as part of a project this semester, and there are only about five weeks left in the term.

Thanks,

Jeff

Here's a post I made a while back that is at least somewhat related to your ideas, if you're looking for some use-cases. In short, it would be useful if it was possible to do filtering on the aggregate results (e.g. only return rows where sum(x) > [some other number]).

http://drupal.org/node/337377

subscribing - I'd like to see this too

okay! Sorry for the incredibly long delay here, I'm buried in this darn queue.

In order for Views to properly support group by, here is what needs to happen:

The support for aggregate function in $query->add_field() needs to be expanded. Right now you can tag whether or not it is an aggregate, but really we need to be able to just pass that in.

The use of 'group by' needs to be a basic field on the view, probably similar to view type. It's one of those fundamental decisions that probably can't be changed when a View is created.

Every handler/argument/filter/field/sort needs to have a flag on it to state whether or not it supports aggregate functions. If it does, then the options screen for that field needs to contain a select widget which contains "Group by this field" and a list of aggregate functions (count, sum, min, max...are there others?)

If a field does not support aggregate functions, then that select must contain only 'group by this field' and it should act appropriately if the group by setting is on in the view.

Every handler that supports group by must then be modified to take into account this aggregate when adding itself to the query. This is going to be the tedious part because there are 130 or so handlers in Views and probably most of them will need to be examined.

We'll have to up the API version of course. Should be fun stuff!

Making this the master issue for dealing with group by, and changing the title appropriately.

Some of this is in Views Calc now, the Views Calc table has options for the aggregation you want to use for each column, so you can select that you want to 'SUM', 'COUNT', etc for each field in the table. I did it not by altering the handlers themselves but by letting you identify in the view which fields need to be aggregated. Views Calc creates a regular view for the main table and then adds an embedded view for each subtotal. I had been planning to add another option let you choose to hide the main table and show the aggregated values only.

Views Calc has another plugin to create groupby totals for the Charts module, That takes a different approach where you identify the field to aggregate on and the field to do the computation on (they are not necessarily the same, you could group by User ID and find the MIN created date).

The aggregated value then also needs some formatting information -- COUNT is always an integer value but SUM needs to pass through a theme to look the same as the individual values within it so has the right number of decimals or the needed prefixes or suffixes.

I also found it was nice to let people right, left, or center justify table columns, because numeric totals look very odd left-justified.

Just some food for thought. Maybe some of the work in Views Calc could be helpful here.

Also Views Calc has a 'Computed field' -- you can create as many custom fields as you need built out of SQL, like 'CONCAT('field_first_name', ' ', 'field_last_name')' or whatever. Then you can add those fields to your view and aggregate by them as well.

Indeed it seems like a lot of it may be necessary. We should get the author of the views_groupby module to weigh in as well.

It seems like most people have been thinking about this from a bottom-up perspective. By that I mean the user would design the view for the detailed information (Continuing my example, the user would make a view to list all employee paychecks for a period). Then, they would select an option to group by department, somehow adding a calculation that performs SUM(pay_amount) for each department. My question is, how would the user customise the aggregated lines? If they wanted to add fields, like department id, department name, or business unit, could they?

Another idea would be to implement in a top-down way, where a view listings departments really drives the process. The user would create this view, and then add a 'sub-view' (sub-view would be a new view element, like 'relationship' or 'filter'). As they create this, they would select a view to use (one that lists paychecks), and select what arguments need to be passed to the subview. These would include both static arguments (arguments of the main view, exposed filter data) as well as dynamic arguments (the nid of the department for the current row). This sub-view would then be executed for each row in the main view. The user would be able to choose to display this information (resulting in a grouped view), or not (used only for calculated fields).

Then, calculated fields could be added to the main view. These would be linked to sub-views much the same way that fields can be linked to relationships now. A field like 'sum' might ask the user for a) the sub-view to link to b) the field to sum, and c) formatting information.

Any thoughts on this? Also, on a different subject, merlin pointed out that I had been misusing the term "Summary View." Could someone please explain what a summary view is, or point me to a reference?

Thanks,

Jeff

There are lots of different ways this could be implemented, it will take some thought to figure out the most flexible interface. There will be times when you want to see the details with summary totals after, there will be times you want to see only summary totals. I've even wondered if each group by query should be an attachment to the main view instead of trying to get them into the same view (although then it's hard to style the whole thing into a single table).

Adding new, additional fields to the summary line would add a lot of complexity, I'm not sure it's worth trying to get incorporated. We already have a system for selecting fields for the view, the easiest thing is for those to also be the fields used in the summary.

As for 'Summary views', create a view with arguments and choose the option on the arguments to display the results in a summary and look at the query this creates and the way it is displayed. That's the current 'group by' behavior.

Here's another thing to consider: http://www.oreillynet.com/pub/a/mysql/2007/03/29/emulating-analytic-aka-...

Unfortunately, MySQL doesn't support Analytic functions (yet?), but they are very powerful and that article might have some ideas that prove useful in extending Views.

And yes, I've written many SQL statement generator over the years, so I've got a pretty good idea of how quickly thing can get pretty complex. Unfortunately my skills and patience with PHP are pretty low at the moment. Maybe after I've had a crack at some of the simpler module ideas I'm thinking about I might be ready to lend a hand here. For now the best I can do is lob unsolicited ideas from the sidelines.

Colin

following this issue. Even for basic stuff like tell me how many people voted on option1 in this poll, can't be done yet :( Going to investigate the other modules, but have a feeling that there is no magik bullet.

I think views calc can answer the 'how many people' question.

I achieved "similar" functionality using http://drupal.org/project/views_groupby
Currently it don't support SUM() but adding it is easy.

So, of the MySQL Aggregate functions and PostgreSQL Aggregate functions, which ones would we support?

-Corey

adding a tag and pumping to 3.x

I've had a go at making a patch that focuses more on the aggregation and not the grouping thus far -- I've attached a patch that will let you do (hopefully) many aggregating functions, such as COUNT(), SUM(), AVG(), MAX() and MIN(). It's quite simple, and thus almost certainly buggy, but I'd be interested to see what people thought. It applies to 6.x-2.x (the latest release), since the CVS version I checked out had errors (possibly/probably my bad).

To use:

1. Add a field to a query.
2. Check the 'aggregate this field' checkbox.
3. Pick an aggregation function.

Views will automatically group any non-aggregated fields (inherited from their support for count queries). I've yet to add more controls for grouping. Maybe it's complete crap, maybe it's a start if someone can re-roll / let me know where I should be looking in CVS.

lunaris

Subscribing.

Subscribing

Subscribing

@lunaris

Applied your patch in #24 -- unfortunately this method seems to break down when relationships are involved. The problem seems to be that when aggregating on a field in a related table, the foreign keys are included in the 'group by' clause which basically prevents any aggregation from taking place.

This definitely reemphasizes the need for some level of control over the fields included in the 'group by' clause.

I've rerolled lunaris's patch in #24 with the following additions:

1) You can now flag fields for grouping under the field options dialog
2) Added a 'Concatenate' aggregation type (MySQL) that wraps the field in GROUP_CONCAT() and out puts a comma delimited value list

When one or more fields are flagged for grouping, views default 'group by' functionality is overridden (i.e. the only fields included in the group by clause are those that have been explicitly flagged within the view). These changes are all MySQL specific, and take advantage of the fact that MySQL does not require all non-aggregate fields to be included in the group by clause.

@ISPTraderChris: I think that you should use a previous version of views, or maybe views 2, because this patch doesn't apply.

I have rerolled this patch to views 3.x and make some testing. It seem to be working. Even with relathionships.

It's important test it with PostgreSQL i'm only using MySql.

Also I have removed GROUP_CONCAT() because it isn't compatible with PostgreSQL, however there is a way to implement it. More information here

Changing status.

@dagmar - thank you. The patch was against the current version of views2 - I will have to go back and see where my patch creation went wrong.

I reviewed your link on PostgreSQL concatenation and it seems straight forward -- basically the two concatenation functions would be:

1) For MySQL: GROUP_CONCAT(field SEPARATOR delimiter)
2) For PostgreSQL: ARRAY_TO_STRING(ARRAY_AGG(field), delimiter)

This should be straight forward to implement, but I am not sure how to check whether MySQL or PostgreSQL is being used?

Patches for new features have to be designed for 3.x version. My last patch is a rellol of yours for views 3.x.

but I am not sure how to check whether MySQL or PostgreSQL is being used?

You can use:

Also we need a bit of documentation for this patch. My english is not good. Who can write it?

I will volunteer to provide the documentation. I am still new to the drupal contribution process -- can you point me to a few examples of the documentation type and format required? Also how shall I submit the documentation?

Sub

This is such a needed enhancement for Views. Can't wait. In fact I didn't. Basic features in patch in 29 worked great in views 2 (mysql)...I actually didn't realize this was for 2. Finally some good reporting abilities with views! You all rock.

Yeah, patch #29 does EXACTLY what I needed it to...Thanks.

sub

Is there any chance of retrofitting this to views 2? I'll test some scenarios with patch 29 if that helps.

nice one! patch 29 worked fine. i just use it to group by the node.nid in order to have just one row for each node after some joins.

I had to change this to $groupby_fields[] = $string; to get it to work with a CCK field. MySQL at least doesn't like to have aliases in group bys, just as in Wheres.

This review is for #29 bot also applies to #30.

I'm on crack. Are you, too?

Here's a little bit changed patch (against views 2.x) that abstracts the available aggregation functions a bit better.

Subscribing and hoping to be able to help with some testing.

Subscribing

subscribing - interesting feature I would like to follow the development of.

I ran into a problem while trying to rework this that required rethinking it pretty seriously.

The problem is this: I wanted to do more than display aggregations. We need to be able to sort and filter on them.

In doing this, I discovered that if you change node.title into COUNT(node.title) the operations on node.title are no longer relevant. "contains" and "starts with" don't really work when what you really want to do is work on numbers.

Likewise, in fields 'link this field to its node' isn't relevant when you have 3 rather than 'E Pluribus Unum'. And you don't need to go to great lengths to add additional fields needed to render something when you're just counting how many instances are in the database.

What I ended up deciding was that the group by system needs to be able to introduce alternate handlers that know what the new data is going to look like.

In order for all this to work right, I bumped up the Views API version on this. That means no modules will put their stuff in until they're retooled for this. All they really need to do, for this patch, is to correctly mark themselves as being aggregatable or not. However I suspect there will be a few more wrinkles when we get to CCK. I hope to take a crack at that tomorrow.

Right now, fields mark themselves as not being valid with group by queries by adding 'no group by' into their definition. I did this with taxonomy: all terms as a test, but we need to go through (sigh) every field and determine if it should be excluded with group by queries.

I've no idea how this patch will react to a wide assortment of Views, but there's still a bit more work to do anyway:

Here's a screenshot:

Subscribing - This will be amazing

Here's an updated patch for Views and a patch for CCK to utilize the new feature.

I imagine there's a whole lot of edge cases that will have to be filled in as we encounter them.

hmm... subscribing as it seems related to #616370: node.type is added to SELECT after being added as a filter

In regards to the patch for CCK... I haven't tested, just browsed the patch, so I may be missing something, but it seems to me if we do not pass a pseudo node to content_format(), then we'll break modules that take advantage of the $node object passed to hook_field_access(). Please, see #514452: Add new argument $node to content_access() to enhance the context for hook_field_access()

We may also break other things if we call content_format() without a pseudo node without the type (maybe implementations of field formatters, hook_field('sanitize'), ...).

Markus: Note that when using group by, the idea of a node may be gone.

Imagine grouping on a taxonomy-like field. Lots of nodes may have the text field "Cool"; when you render it, there isn't a single node. Yes, you will lose the distinctiveness of nodes, but that is the point of grouping, and is a necessity.

I will probably commit this soon so I would love some comments on the patch.

It should be:

I have installed this patch in views 3. I make tests for Node type and Node title, and also an integer cck field.

There is an sql error when users select a group by sort different than defined in fields.

Steps to replicate:

Fields:
Node: Type
COUNT(Node: Title)

Order:
COUNT(Node: Type) asc

This settings produce this SQL:

And this error:

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 'ORDER BY

However order by COUNT(node.type) works fine.

I can reproduce the error if i have distinct activated

Subscribing by positing a patch which fixes the first issue :)

Here is a patch which fixes the sql error with distinct, but i'm not really sure, whether this is the right method:

The DISTINCT problem was a little deeper than that, and existed because I had stopped putting the base field in automatically. When a query is set distinct, though, the base field still needs to appear. An add_field fixed that, except that we also need to re-order DISTINCT fields to the beginning, as mysql doesn't like them not being at the beginning.

I also added some uniqueness checking on fields, which could have some wide-ranging effects. I then committed the patch because maintaining this is unwieldy. Views 3 is now less stable, probably, but has group by. Because of htis, I tagged an alpha1 prior to the commit so people can have a solid point for actually using Views 3.

Here's the patch for the purposes of porting to 7.x

Mh. I think i have to invest some time to figure this out

Is it transparent to CCK fields, or do we need to do anything to CCK field handlers in order to keep compatibility with both Views 2 and 3? (I'm not using Views 3, so it's not easy for me to see)

Related issue in CCK queue: #616370: node.type is added to SELECT after being added as a filter

markus: the patch I posted for CCK will need to be applied to CCK. It should work against both Views 2 and Views 3, though moving forward that may become a little cumbersome, but I will do my best to try not to make your life too hard keeping up with this.

It does need to be tested against Views 2 to make sure it breaks nothing.

Ok. i took a manuall diff between two versions.

Here is the first version BUT! i didn't converted yet the hard part, the sql generation, because the 7.x version uses dbtng