Summary views fix not working.

Add the "Node: Distinct" filter to your view. THat usually fixes problems of this nature.

1.6-beta2 or alter should fix the summary + distinct error.

I'm reopening this issue since this is the issue referred to in the commit at http://cvs.drupal.org/viewcvs/drupal/contributions/modules/views/views_q... and I think that commit has broken things. In that commit, anything in the orderby is getting added to the groupby which creates completely mixed up results. This has broken several of my modules that use summary views.

As an example, the following view should create a simple table of node titles summarized by taxonomy term and sorted by sticky and last updated dates. Instead of grouping by taxonomy term, it is grouped by taxonomy term and also sticky and also last updated. Adding last updated to the groupby effectively creates a list of individual items instead of grouping by taxonomy terms. Interestingly, even though the view does NOT ask for node distinct, node distinct has been added to the query too.

I think part of the problem is the following bit in the add_orderby() function, which adds anything in the orderby to the groupby:

if ($this->groupby) {
  $this->add_groupby($as);
}

That seems an odd thing to do and I'm not sure when it would work right.

The other part of my problem with summary views now is that I am getting a DISTINCT when I don't want it, but I couldn't figure out what was adding the DISTINCT clause to the query since the view itself was not asking for it.

The view:

$view = new stdClass();
  $view->name = 'test';
  $view->description = '';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = 'test';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = 'No items found for this gallery.';
  $view->page_empty_format = '1';
  $view->page_type = 'table';
  $view->url = 'test';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '10';
  $view->block = TRUE;
  $view->block_title = 'Galleries';
  $view->block_header = '';
  $view->block_header_format = '1';
  $view->block_footer = '';
  $view->block_footer_format = '1';
  $view->block_empty = '';
  $view->block_empty_format = '1';
  $view->block_type = 'list';
  $view->nodes_per_block = '5';
  $view->block_more = TRUE;
  $view->block_use_page_header = FALSE;
  $view->block_use_page_footer = FALSE;
  $view->block_use_page_empty = TRUE;
  $view->sort = array (
    array (
      'tablename' => 'node',
      'field' => 'sticky',
      'sortorder' => 'DESC',
      'options' => '',
    ),
    array (
      'tablename' => 'node',
      'field' => 'changed',
      'sortorder' => 'DESC',
      'options' => 'normal',
    ),
  );
  $view->argument = array (
    array (
      'type' => 'taxid',
      'argdefault' => '6',
      'title' => '%1',
      'options' => '',
      'wildcard' => '',
      'wildcard_substitution' => '',
    ),
  );
  $view->field = array (
    array (
      'tablename' => 'node',
      'field' => 'title',
      'label' => '',
      'handler' => 'views_handler_field_nodelink',
      'options' => 'link',
    ),
  );
  $view->filter = array (
    array (
      'tablename' => 'node',
      'field' => 'status',
      'operator' => '=',
      'options' => '',
      'value' => '1',
    ),
    array (
      'tablename' => 'node',
      'field' => 'type',
      'operator' => 'OR',
      'options' => '',
      'value' => array (
  0 => 'content_pictures',
),
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(node);
  $views[$view->name] = $view;

The query it creates:

SELECT term_data.name, term_data.weight, term_data.tid, count( DISTINCT(node.nid)) AS num_nodes, node.sticky AS node_sticky, node.changed AS node_changed_changed, node.title AS node_title, node.changed AS node_changed FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_data term_data ON term_node.tid = term_data.tid WHERE (node.status = '1') AND (node.type IN ('content_pictures')) GROUP BY term_data.name, node_sticky, node_changed_changed ORDER BY node_sticky DESC, node_changed_changed DESC LIMIT 0, 10

Forgot to change the version and altering the title a bit.

I did some more digging into the original commit to see if there was any way to preserve the original intent but not break summary views. The commit message says:

#134375: (by vhmauery) Postgres fix, adds orderby to groupby if there is already a groupby.

This is the issue referred to by that commit, but there is no patch by vhmauery here and I couldn't find one anywhere else. The Postgres fixes in general have been to make sure that fields in orderby and sortby are also in the select list, which is handled already in the sortby function, so I'm pretty sure that the bit of code added by this commit is not needed to fix the postgres problem and I think it would be safe to just rollback the commit. At any rate, that gets summary views working again for me.

I don't know that this will fix the original issue, but the committed fix didn't seem to do that anyway.

As I dig into this more, I probably should have opened a new issue for the problem with the commit and also reopened this issue which still needs a solution. Sorry for any confusion...

subscribing as this is now effecting me.

leobaby: This may all be caused by, or made worse by, my use of multiple locations for each node. If I display field location:street in the list, I get multiple multiples. But both multi select taxonomy and multiple locations are relatively important.

There's a CCK setting you need to ensure that multiple fields don't cause Views to bring up multiple records; I think it's 'group multiple fields' or something along those lines. I need to play with this so I can answer that question better.

With multi-select taxonomy, though, I'm not able to duplicate your problem at all, so I don't have further advice.

KarenS: The postgres patch you're looking for is http://drupal.org/node/121144 -- I guess I misnumbered it on the checkin. Bad me. IN any case, the problem with the view you posted is that the sort criteria you put really aren't valid. Apparently in PGSQL if you have a group by, you can't sort by items that are NOT part of the group by (since that sort will have no meaning). In the view you posted, you're stuck with summary, sort ascending or summary, sort descending. But summary, sorted as view isn't going to work very well at all, unfortunately.

If there's something still wrong with 121144 let's take it there.

This still seems to be a problem even after updating to views-5.x-1.6-beta5.tar.gz

There's a CCK setting you need to ensure that multiple fields don't cause Views to bring up multiple records; I think it's 'group multiple fields' or something along those lines. I need to play with this so I can answer that question better.

Where is this setting? In CCK or in Views. If it is in views, I can't seem to figure out where it is. It seems to be duplicating on the Taxonomy terms for me now.

"CCK Setting".

Thus: Not Views.

Actually it's a setting defined by CCK but exposed in the Views UI : When adding a CCK field to your Views "Fields", you have a dropdown allowing you to chose 'Group multiple values' (default) / 'Do not group multiple values'.

I've just put up a new Drupal 5 with current downloads of views, views bonus, taxonomy etc (all my taxonomies are free tagging if it makes any difference)

The directory / taxonomy_directory options is horribly broken - I'm getting 50 links all to the same letter all with a count of 1 with duplicate nodes all over the place!!

Same problem here. Searching for a solution.

To fix the taxonomy directory to work with the latest changes, open the view and edit it and change 'Summary, sorted as view' to 'Summary, sorted ascending' in the 'Arguments' area.

I just committed a fix to the default view in the bonus pack to do the same thing, but you'll have to make that change in any version of the view you already have in your system.

Thanks Karen. That seemed to do the trick for me!

Summary views is not working for me either. Changing 'Summary, sorted as view' (which used to work fine) to 'Summary, sorted ascending' in the 'Arguments' area didn't work as a fix for me.

What's the fix for this?

I have a fairly simple view that shows blog posts with:
- a taxonomy argument
- a vocab filter to get only terms from the right vocab in the summary
- sorted by node creation

and the problem is as described above:
- with the distinct filter, terms repeated, one node only per term
- without the distinct filter, nodes are repeated

Hacking about with the query shows that the problem in my case is the node.nid in:
GROUP BY node.nid, term_data.name, term_data_weight,

This is put in by the Node: Distinct filter.

I've no idea why the distinct filter needs to group on nids... removing this line from views_node.inc doesn't cause any side-effects (yet!!)
#$query->add_groupby('node.nid');

no real work is going into views 1 at this point - I don't see the changes getting worked on/committed.