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.
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.
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:
You will have to determine whether this serves your purpose. There are some likely issues:
But if it seems promising, here's the query I ran on the database:
SELECT
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
FROM
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
WHERE
users.uid NOT IN (0,1,32,33,44) # undesirable admin/test users
GROUP BY email
ORDER BY full_name
Comments
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:
<?php/**
* 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.