Search code examples
sqldrupaldrupal-7drupal-commerce

Querying Users based on role and billing address drupal

I am trying to write a query that displays a list of users based on their role and billing address.

I am getting results back but some of the data is wrong. I know for instance that some of the users returned live in the UK but a US address is showing.

The query I have is 

`SELECT u.uid, u.name, mail,  commerce_customer_address_country, commerce_customer_address_postal_code, fdcca.entity_id FROM users u  
INNER JOIN users_roles ur ON ur.uid = u.uid
INNER JOIN user_address ua ON ua.uid = u.uid
INNER JOIN field_data_commerce_customer_address fdcca ON ua.address_id = fdcca.entity_id   
WHERE ur.rid = 11 AND commerce_customer_address_country != 'GB' 
LIMIT 50`

I think I have got the joins wrong but I am a bit lost, and would appreciate any tips.

Solution

  • Use views module instead and function: https://api.drupal.org/api/views/views.module/function/views_get_view_result/7