0

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
4
  • Try to only group by users_field_data.mail GROUP BY users_field_data.mail Since the uid is not unique, you will otherwise always get all results.
    – Chris4783
    Oct 5, 2022 at 13:24
  • Sorry for my late answer... How can I remove the other GROUP BY from within mymodule_views_query_alter. I can see only an addGroupBy function in core/modules/views/src/Plugin/views/query/Sql.php (no way to remove a Group By)
    – Baud
    Oct 6, 2022 at 10:47
  • 1
    Try $query->groupby = ['users_field_data.mail']; instead of addGroupBy.
    – Chris4783
    Oct 7, 2022 at 6:48
  • Many thanks! it is working. Could you convert your comment in an answer in order for me to credit you.
    – Baud
    Oct 7, 2022 at 7:45

1 Answer 1

1
+50

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.