Vote up!
Vote down!

How do I add addresses from customer profiles to a View of all users?

I'm trying to create an export of sorts of all users in website, along with at least one address from their customer profiles.

I just can't quite figure out how to achieve this in a View of users.

Asked by: rachelnorfolk
on August 23, 2013


I know this is an old topic but I was able to do this fairly easily, by using hook_views_data_alter to connect customer profiles to the user table:

 * Implements hook_views_data_alter().
function MYMODULE_views_data_alter(&$data) {
  if (isset(
$data['users'])) {
// Add a relationship for users' profiles
$data['users']['uid_commerce_customer_profile'] = array(
'title' => t('Customer profile'),
'help' => t('Relate customer profiles for the user they belong to. This relationship will return one record for each profile belonging to the user.'),
'relationship' => array(
'handler' => 'views_handler_relationship',
'base' => 'commerce_customer_profile',
'base field' => 'uid',
'field' => 'uid',
'label' => t('Profile'),

This makes a 'Customer profile' Relationship available to any view that uses the Users table. Once the relationship is added to a view, all fields on the Customer Profile become available for output, filtering, sorting etc.

- kingandy on September 25, 2017

2 Answers

Vote up!
Vote down!

There is no way to do this at present using Users as the base entity type of the View. It would be possible to create a View of Customer Profiles and establish a relationship to the User table to include user information on the View, but you'll still face the issue of needing to limit it to a single customer profile per user. How would you determine which one to use in the event that a customer has more than one? (e.g. if you're using Commerce on a site without using Commerce Addressbook and the user has ordered more than once.)

Ultimately that data problem will be up to you to resolve. One suggestion might be to add a customer profile reference field to your user accounts and update it to reference the customer profile used on the customer's most recent order. This would basically treat the latest billing address as their billing address, but it's still not foolproof if someone orders on behalf of someone.

Finally, if you have some coding chops, you do have the option of adding a Views relationship to the User table that joins to the appropriate Customer Profile record. We do this in core with the payment_transaction_representative handler found in commerce_payment.views.inc, which lets you establish a relationship from Orders to a single representative Payment Transaction entity (using the Views relationship handler "groupwise_max"). You might be able to do something similar if that meets your business case.

Ryan Szrama
Answer by: Ryan Szrama
Posted: Aug 28, 2013
Vote up!
Vote down!

I've been trying to do a very similar thing. We need names & emails for users. In the end, I managed to dig through the database and assemble a mySql query that:

  • Pulls the 'name' field from the billing address
  • Joins that with the user table and pulls that email address

You will have to determine whether this serves your purpose. There are some likely issues:

  • The billing address may not be the user's actual name
  • The billing address name may not be a name at all (many users stick the first line of their address in here)
  • The email they create their account with may not be the one you want

But if it seems promising, here's the query I ran on the database:

users.uid AS uid,
commerce_addr.entity_id AS profile_id,
commerce_addr.commerce_customer_address_name_line AS full_name,
users.mail AS email
field_data_commerce_customer_address AS commerce_addr
INNER JOIN commerce_customer_profile ON commerce_addr.entity_id = commerce_customer_profile.profile_id
INNER JOIN users ON commerce_customer_profile.uid = users.uid
users.uid NOT IN (0,1,32,33,44) # undesirable admin/test users
GROUP BY email
ORDER BY full_name
Answer by: doub1ejack
Posted: Dec 18, 2013