Duplicate nodes in view when field with multiple values in field set

All CCK Fields normally have a 'group multiple values' flag which handles this. In any case, your'e talking about non core Views fields; it's up to the handlers of the module that provides the field to provide all processing. That's why there's this stern warning in the submission guidelines:

If you only read one of these submission guidelines, read this one. (Then please read the rest) If your problem deals exclusively with fields or filters provided by another module (CCK, Image, VotingAPI are common) please post the issue under the queue for that module first; all modules are responsible for telling Views about their own fields. It's possible Views IS at fault, but the module maintainers are the best people to make that determination, and they can kick the issue into the Views queue with an explanation of what's wrong if that is the case.

Finally, the distinct fields adds the DISTINCT keyword to the query. But some queries are simply complex enough that the database fails. I don't know why, nor is there much I can do about it. If the database doesn't actually make it DISTINCT, that's how the cookie crumbles.

i think this is the same issue as this

http://drupal.org/node/298646

the problem with "group multiple values" is that it can only make the "date field" to display only one date
but not preventing the "node" being duplicated several times in the views result due to date repeat field

my SQL output from views looks like this:


SELECT
DISTINCT(node.nid) AS nid,
node_data_field_date_repeat.field_date_repeat_value AS node_data_field_date_repeat_field_date_repeat_value,
node.title AS node_title FROM mcs__node node
LEFT JOIN mcs__content_field_date_repeat node_data_field_date_repeat ON node.vid = node_data_field_date_repeat.vid
WHERE (node.type in ('activity')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_date_repeat.field_date_repeat_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2008-11-05')


i have only chosen node title as the view's output field
but in the SQL, "node_data_field_date_repeat.field_date_repeat_value" is also output
and after executing the SQL in phpMyAdmin directly
i found that it is the field_date_repeat_value field preventing the DISTINCT to work,
i.e. once the field_date_repeat_value item is removed, the DISTINCT works properly

is the field_date_repeat_value item used for any purpose?
can an option be added in the date filter to choose whether output duplicated nodes or not?

i investigated deeper and find that there is a difference between the sql for DISTINCT in views 5.x-1.6 and 6.x-2.1
the 5.x-1.6 SQL adds " GROUP BY node.nid " at the end of the DISTINCT query while 6.x-2.1 does not

if i add that manually to my SQL from 6.x-2.1 and execute in phpMyAdmin, the DISTINCT works correctly
so the correct SQL for the one in #4 should be

SELECT
DISTINCT(node.nid) AS nid,
node_data_field_date_repeat.field_date_repeat_value AS node_data_field_date_repeat_field_date_repeat_value,
node.title AS node_title FROM mcs__node node
LEFT JOIN mcs__content_field_date_repeat node_data_field_date_repeat ON node.vid = node_data_field_date_repeat.vid
WHERE (node.type in ('activity')) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_date_repeat.field_date_repeat_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2008-11-05')
GROUP BY node.nid 

i don't know if it's a bug or a change in the new version of views
but this matches some other statements i found from Google
which said that MySQL DISTINCT doesn't work correctly if only DISTINCT is used without GROUP BY
i think this may also be why the DISTINCT not working in some other cases
as stated in the DISTINCT option description in views setting page

This is not about DISTINCT. DISTINCT is not the answer, it is up to the handler to provide proper SQL that will not bring duplicates. DISTINCT is a highly unreliable solution and I really wish it simply didn't exist, because when it doesn't work people file bugs against Views.

The group by thing was a hack, but it has some other ugly side effects, and I chose not to reproduce it in Views 2, and I stand by that choice. Again, it is up to date.module (or CCK) to provide SQL that will not cause duplicates when it knows that there are multiple fields. This is doable, it just sucks.

attached patch add an option to the date filter, allowing to disable date field data in the query result
and thus make the distinct function to work

in fact after inspecting the code
i think it is better to directly remove the line 430

$this->query->add_field($field['table_name'], $field['field_name']);

as the "group values" function of cck date repeat field still works even if the line is removed
but produces duplicated nodes if the line is there

a update of the old patch, which fixes the issue that the setting value is not displayed properly

This is no longer needed. I've made changes to the way the filter works that make adding the field unnecessary, so it isn't done any more.

1) it works with distinct without random filter
2) it works with distinct plus random filter when a node type filter is used with only one node type is selected
3) but not works with distinct plus random filter when a node type filter is used with more than one node type is selected
4) but not works with distinct plus random filter when no node type filter is used

don't know if this is related to date since it works in case 2

below is the sql generated by view

SELECT DISTINCT(node.nid) AS nid, 
image_attach.iid AS image_attach_iid, 
node.title AS node_title, 
node_data_field_content.field_content_value AS node_data_field_content_field_content_value, 
node_data_field_content.field_content_format AS node_data_field_content_field_content_format, 
node_data_field_content.nid AS node_data_field_content_nid, 
node.type AS node_type, 
node.sticky AS node_sticky, 
RAND() AS _random 
FROM mcs__node node  
LEFT JOIN mcs__content_field_date_repeat node_data_field_date_repeat ON node.vid = node_data_field_date_repeat.vid 
LEFT JOIN mcs__image_attach image_attach ON node.nid = image_attach.nid 
LEFT JOIN mcs__content_field_content node_data_field_content ON node.vid = node_data_field_content.vid 
WHERE ((node.status <> 0) AND (node.type in ('activity', 'friday_worship', 'gospel_lunch', 'joyful_journey', 'lunch_hour', 'lunch_power'))) 
AND (DATE_FORMAT(STR_TO_DATE(node_data_field_date_repeat.field_date_repeat_value, '%Y-%m-%dT%T'), '%Y-%m-%d') >= '2009-01-15') 
ORDER BY node_sticky DESC, _random ASC

i tried to modify the sql and execute it directly in phpmyadmin,
and gave the same duplicated rows output

Bump-Subscribe!

I've recently come across the exact same issue, but for now I'll cope with duplicated output. Manually adding group by to the query run directly DOES seem to provide exactly what I'm looking for, but I tend to heed the warnings of the wise merlinofchaos when given, so here's hoping there's a better solution...

This should work fine now if you use the 'group' option for multiple values in the latest -dev code. I've been making fixes for the last couple days, so you need the very latest to be sure you have what I have.

Is it also applied to the latest 5.x.dev?
In that version Views still shows repeating nodes when the nodes contains multiple dates.
Do I have to open a new issue, or do I set this one to active?
Regards

EDIT:
OK I see the D6 logic is not the same as in D6. The problem disappears when I switch back to 5.2, so I will file an issue for D5.

Using Date: 6.x-2.x-dev dated March 9, 2009.
Still not working.

$view = new view;
$view->name = 'schedule';
$view->description = 'Schedule of Events';
$view->tag = 'event';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'title' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
  ),
  'field_ev_date_value' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'label_type' => 'none',
    'format' => 'event_date',
    'multiple' => array(
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_to' => '',
      'group' => 1,
    ),
    'repeat' => array(
      'show_repeat_rule' => '',
    ),
    'fromto' => array(
      'fromto' => 'both',
    ),
    'exclude' => 0,
    'id' => 'field_ev_date_value',
    'table' => 'node_data_field_ev_date',
    'field' => 'field_ev_date_value',
    'relationship' => 'none',
  ),
  'field_ev_location_value' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'label_type' => 'none',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_ev_location_value',
    'table' => 'node_data_field_ev_location',
    'field' => 'field_ev_location_value',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'field_ev_date_value' => array(
    'order' => 'ASC',
    'delta' => '0',
    'id' => 'field_ev_date_value',
    'table' => 'node_data_field_ev_date',
    'field' => 'field_ev_date_value',
    'relationship' => 'none',
  ),
));
$handler->override_option('arguments', array(
  'date_argument' => array(
    'id' => 'date_argument',
    'table' => 'node',
    'field' => 'date_argument',
  ),
));
$handler->override_option('filters', array(
  'status' => array(
    'operator' => '=',
    'value' => '1',
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'status',
    'table' => 'node',
    'field' => 'status',
    'relationship' => 'none',
  ),
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'event' => 'event',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'relationship' => 'none',
  ),
  'date_filter' => array(
    'operator' => '>=',
    'value' => array(
      'value' => NULL,
      'min' => NULL,
      'max' => NULL,
      'default_date' => '',
      'default_to_date' => '',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'date_fields' => array(
      'node_data_field_ev_date.field_ev_date_value' => 'node_data_field_ev_date.field_ev_date_value',
    ),
    'date_method' => 'AND',
    'granularity' => 'month',
    'form_type' => 'date_select',
    'default_date' => '',
    'default_to_date' => '',
    'year_range' => '-3:+3',
    'id' => 'date_filter',
    'table' => 'node',
    'field' => 'date_filter',
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('title', 'Schedule');
$handler->override_option('use_ajax', TRUE);
$handler->override_option('items_per_page', 5);
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
  'type' => 'ul',
));
$handler->override_option('row_options', array(
  'inline' => array(),
  'separator' => '',
));
$handler = $view->new_display('block', 'Block', 'block_1');
$handler->override_option('block_description', 'Schedule');
$handler->override_option('block_caching', -1);
$handler = $view->new_display('page', 'Page', 'page_1');
$handler->override_option('fields', array(
  'title' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 1,
    'exclude' => 0,
    'id' => 'title',
    'table' => 'node',
    'field' => 'title',
    'relationship' => 'none',
    'override' => array(
      'button' => 'Use default',
    ),
  ),
  'field_ev_date_value' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'label_type' => 'none',
    'format' => 'event_date',
    'multiple' => array(
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_to' => '',
      'group' => 1,
    ),
    'repeat' => array(
      'show_repeat_rule' => '',
    ),
    'fromto' => array(
      'fromto' => 'both',
    ),
    'exclude' => 0,
    'id' => 'field_ev_date_value',
    'table' => 'node_data_field_ev_date',
    'field' => 'field_ev_date_value',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
  'field_ev_location_value' => array(
    'label' => '',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'label_type' => 'none',
    'format' => 'default',
    'multiple' => array(
      'group' => TRUE,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => FALSE,
    ),
    'exclude' => 0,
    'id' => 'field_ev_location_value',
    'table' => 'node_data_field_ev_location',
    'field' => 'field_ev_location_value',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'field_ev_fasilitator_nid' => array(
    'label' => 'Fasilitator',
    'alter' => array(
      'alter_text' => 0,
      'text' => '',
      'make_link' => 0,
      'path' => '',
      'alt' => '',
      'prefix' => '',
      'suffix' => '',
      'help' => '',
      'trim' => 0,
      'max_length' => '',
      'word_boundary' => 1,
      'ellipsis' => 1,
      'html' => 0,
    ),
    'link_to_node' => 0,
    'label_type' => 'widget',
    'format' => 'noderef_comma',
    'multiple' => array(
      'group' => 1,
      'multiple_number' => '',
      'multiple_from' => '',
      'multiple_reversed' => 0,
    ),
    'exclude' => 0,
    'id' => 'field_ev_fasilitator_nid',
    'table' => 'node_data_field_ev_fasilitator',
    'field' => 'field_ev_fasilitator_nid',
    'override' => array(
      'button' => 'Use default',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('items_per_page', 10);
$handler->override_option('use_pager', '1');
$handler->override_option('path', 'schedule');
$handler->override_option('menu', array(
  'type' => 'normal',
  'title' => 'Schedule',
  'description' => '',
  'weight' => '0',
  'name' => 'primary-links',
));
$handler->override_option('tab_options', array(
  'type' => 'none',
  'title' => '',
  'description' => '',
  'weight' => 0,
));

SQL Generated from Views:

SELECT node.nid AS nid, node.title AS node_title, node.vid AS node_vid, node.type AS node_type, node_data_field_ev_location.field_ev_location_value AS node_data_field_ev_location_field_ev_location_value, node_data_field_ev_location.field_ev_location_format AS node_data_field_ev_location_field_ev_location_format, node_data_field_ev_location.nid AS node_data_field_ev_location_nid, node_data_field_ev_date.field_ev_date_value AS node_data_field_ev_date_field_ev_date_value FROM node node  LEFT JOIN content_field_ev_date node_data_field_ev_date ON node.vid = node_data_field_ev_date.vid LEFT JOIN content_type_event node_data_field_ev_location ON node.vid = node_data_field_ev_location.vid WHERE ((node.status <> 0) AND (node.type in ('event'))) AND (DATE_FORMAT(STR_TO_DATE(node_data_field_ev_date.field_ev_date_value, '%Y-%m-%dT%T'), '%Y-%m') >= '2009-01') ORDER BY node_data_field_ev_date_field_ev_date_value ASC

No distinct here, is it something that I did wrong ??

Ok, Found the distinct configuration..
So, there EXISTS Distinct now at the SQL, but still the duplicated contents being outputted :)

Any help appreciated. Any guidance on how to make the sql result not be duplicated ??

Unfortunately, like wuf31, using the latest dev release for D6 Date Module didn't solve this issue in my particular case.

I'm running Drupal6.9 and using the following modules:
Date 6.x-2.0
Calendar 6.x-2.0
CCK 6.x-2.x-dev
Views 6.x-2.3

Here is the query for my "Upcoming Events Block" which still lists the node duplicates, despite using the DISTINCT function and grouping multiple date values.

Query	

SELECT DISTINCT(node.nid) AS nid,
   node.type AS node_type,
   node.vid AS node_vid,
   node.title AS node_title,
   node_data_field_event_date.field_event_date_value AS node_data_field_event_date_field_event_date_value
 FROM node node 
 LEFT JOIN content_field_event_date node_data_field_event_date ON node.vid = node_data_field_event_date.vid
 WHERE (DATE_FORMAT(node_data_field_event_date.field_event_date_value, '%Y-%m-%d') >= '2009-03-16')
    AND ((node.status <> 0) AND (node.type in ('event')))
   ORDER BY node_data_field_event_date_field_event_date_value ASC

I've exported and attached my views calendar settings for further review. Perhaps I just did something wrong with my settings that causes the node to still duplicate.

I really love how Date and Calendar module work! Though still very much new to Drupal, I would like to help out as much as I can in figuring out these issues.

Best Regards,
Cecille

See also: http://drupal.org/node/302498 comments 37, 39 and 43 explain that you must be running the dev versions of cck, views, date and calendar.

I am marking as a duplicate, because it certainly seems like it is.