Remove duplicated email in views data export

Question

My site contains users with duplicated emails which is fine for my use case.

I need to export the user's fields into an xlsx but, this time, without duplicated email. For this I have created a Users view with a Data Export display attached to it and I have added the following code:

function mymodule_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view_id == 'my_view' && $display_id == 'my_export') {
    $query->addField('users_field_data', 'mail', '', [
      'function' => 'groupby',
    ]);
    $query->addGroupBy('users_field_data.mail');
  }
}

This kind of code is working well on the node table to remove duplicated ID:

Example:

  $query->addField('node', 'nid', 'node_nid', [
    'function' => 'groupby',
  ]);
  $query->addGroupBy('node.nid');

But it is not working to remove the duplicate emails... any idea on what is wrong?

Here is the query like it is showed by the views GUI:

SELECT "users_field_data"."langcode" AS "users_field_data_langcode", "users_field_data"."uid" AS "uid", "user__field_user_address"."field_user_address_postal_code" AS "user__field_user_address_field_user_address_postal_code", "users_field_data"."created" AS "users_field_data_created", MIN(users_field_data.uid) AS "uid_1"
FROM
{users_field_data} "users_field_data"
LEFT JOIN {user__field_user_address} "user__field_user_address" ON users_field_data.uid = user__field_user_address.entity_id AND user__field_user_address.deleted = '0'
WHERE "users_field_data"."uid" != '0'
GROUP BY users_field_data.mail, users_field_data_langcode, users_field_data.uid, user__field_user_address_field_user_address_postal_code, users_field_data_created
ORDER BY "users_field_data_created" DESC

Answer 1

Since the uid is not unique, you need to remove this group by condition. Try to change your query to

function mymodule_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view_id == 'my_view' && $display_id == 'my_export') {
    $query->addField('users_field_data', 'mail', '', [
      'function' => 'groupby',
    ]);
    $query->groupby = ['users_field_data.mail'];
  }
}

This will only group by the email field and will remove this duplicates.