Views and entity reference: How to avoid duplicates?

Question

I have a content type named »essay« with a entity reference field. Now I want to create a view that show all entities that are referenced by all »essays«. To achieve that I created a view that filters all essays, I added a relation ship to the reference field and now I can show any field of the referenced entities as wished.

But: If two or more essays do reference to the same entity, those will be shown twice or more times. Is there a way to avoid those duplicates?

I do not use any fields from the essay to show in the view, but I do filter the essays by a contextual filter. So the distinct option of the query settings does not work. I think, this is because each row of the view represents an distinct essay and than I join the referenced entities. So from the database point of view the rows are distinct. Am I wrong?

This is a trimmed version of my view:

$view = new view();
$view->name = 'clone_of_objekte';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Clone of Objekte';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */

/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'Objekte';
$handler->display->display_options['css_class'] = 'objects margin-large';
$handler->display->display_options['use_ajax'] = TRUE;
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['pager']['type'] = 'load_more';
$handler->display->display_options['pager']['options']['items_per_page'] = '2';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['pager']['options']['id'] = '0';
$handler->display->display_options['pager']['options']['quantity'] = '9';
$handler->display->display_options['style_plugin'] = 'grid';
$handler->display->display_options['style_options']['columns'] = '2';
$handler->display->display_options['row_plugin'] = 'fields';
$handler->display->display_options['row_options']['hide_empty'] = TRUE;
$handler->display->display_options['row_options']['default_field_elements'] = FALSE;
/* Relationship: Entity Reference: Referenced Entity */
$handler->display->display_options['relationships']['field_object_reference_target_id']['id'] = 'field_object_reference_target_id';
$handler->display->display_options['relationships']['field_object_reference_target_id']['table'] = 'field_data_field_object_reference';
$handler->display->display_options['relationships']['field_object_reference_target_id']['field'] = 'field_object_reference_target_id';
$handler->display->display_options['relationships']['field_object_reference_target_id']['label'] = 'Referenced from field_object_reference';
/* Field: Content: Bilder */
$handler->display->display_options['fields']['field_image']['id'] = 'field_image';
$handler->display->display_options['fields']['field_image']['table'] = 'field_data_field_image';
$handler->display->display_options['fields']['field_image']['field'] = 'field_image';
$handler->display->display_options['fields']['field_image']['relationship'] = 'field_object_reference_target_id';
$handler->display->display_options['fields']['field_image']['label'] = '';
$handler->display->display_options['fields']['field_image']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['field_image']['click_sort_column'] = 'fid';
$handler->display->display_options['fields']['field_image']['settings'] = array(
  'image_style' => 'objects_small',
  'image_link' => 'content',
);
$handler->display->display_options['fields']['field_image']['delta_limit'] = '1';
$handler->display->display_options['fields']['field_image']['delta_offset'] = '0';
$handler->display->display_options['fields']['field_image']['separator'] = '';
/* Sort criterion: Content: Post date */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'node';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Contextual filter: Content: Nid */
$handler->display->display_options['arguments']['nid']['id'] = 'nid';
$handler->display->display_options['arguments']['nid']['table'] = 'node';
$handler->display->display_options['arguments']['nid']['field'] = 'nid';
$handler->display->display_options['arguments']['nid']['default_action'] = 'default';
$handler->display->display_options['arguments']['nid']['default_argument_type'] = 'node';
$handler->display->display_options['arguments']['nid']['summary']['number_of_records'] = '0';
$handler->display->display_options['arguments']['nid']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['nid']['summary_options']['items_per_page'] = '25';
/* Filter criterion: Content: Published */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'node';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = 1;
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Content: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['value'] = array(
  'essay' => 'essay',
);

/* Display: Objects for parent */
$handler = $view->new_display('block', 'Objects for parent', 'objects_views');
$handler->display->display_options['display_description'] = 'Shows the objects that are related to a list of essays';
$handler->display->display_options['defaults']['query'] = FALSE;
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['query']['options']['distinct'] = TRUE;
$handler->display->display_options['query']['options']['pure_distinct'] = TRUE;
$handler->display->display_options['defaults']['arguments'] = FALSE;
/* Contextual filter: Content: Has taxonomy term ID */
$handler->display->display_options['arguments']['tid']['id'] = 'tid';
$handler->display->display_options['arguments']['tid']['table'] = 'taxonomy_index';
$handler->display->display_options['arguments']['tid']['field'] = 'tid';
$handler->display->display_options['arguments']['tid']['default_action'] = 'default';
$handler->display->display_options['arguments']['tid']['default_argument_type'] = 'raw';
$handler->display->display_options['arguments']['tid']['default_argument_options']['index'] = '2';
$handler->display->display_options['arguments']['tid']['summary']['number_of_records'] = '0';
$handler->display->display_options['arguments']['tid']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['tid']['summary_options']['items_per_page'] = '25';
$handler->display->display_options['arguments']['tid']['reduce_duplicates'] = TRUE;
$translatables['clone_of_objekte'] = array(
  t('Master'),
  t('Objekte'),
  t('more'),
  t('Apply'),
  t('Reset'),
  t('Sort by'),
  t('Asc'),
  t('Desc'),
  t('Items per page'),
  t('- All -'),
  t('Offset'),
  t('« first'),
  t('‹ previous'),
  t('next ›'),
  t('last »'),
  t('Referenced from field_object_reference'),
  t('All'),
  t('Objects for parent'),
  t('Shows the objects that are related to a list of essays'),
);

I tried to aggregate the view, but this did not help neither.

Answer 1

I think the answer is: It does not work, as the way I tried to do it, is all wrong. What you have to do to get it work is:

1. Don't list the referencing content, but list the referenced content in a view
2. Add than a relation to the »referencing Content«, not the »referenced content«
3. In Advanced » OTHER section click on ›Query setting‹ item and check the ›Distinct‹ check-box.

To summarize: Now the rows can be distinct as you are listing directly the referenced content type and not the referencing one. Hope that helps and save you same hours!

Answer 2

You can try to add distinct to your query,

1. Go and edit your view

2. In Advanced » OTHER section click on "Query setting"

3. Check "Distinct" check-box.

Answer 3

I'm deleting what I wrote because it may not apply but I'd like to note there is a regular filter called Linking FROM or Linking TO. You should see this if you start typing the name of the field that you set to be an entity reference. This combined with a token may exclude some content you may wish to exclude reducing duplicates. I used [nid].