I'm writing a complex custom query to get order information from a Drupal Commerce install for export.

The table field_data_commerce_customer_address has a column entity_id. I've tried to join that field on order_id, but my results are incorrect. It doesn't match to user id... hmm.

What field should I join entity_id on?

And, more generally, is there a drupal entity informational function that would tell me what columns should be joined to what? Or a master entity table, or anything? Some central location that would tell me things like this so I don't have to dig around?

Looking around the database and googling but haven't found it yet.

3 Answers 3


The entity_id is the ID of the Commerce customer profile (which you can find in the commerce_customer_profile table).

This makes more sense when you look at the type column in the same field table, as it will likely be one of "shipping" or "billing", the default bundles available to the profile entity type.

Profile entities have a uid, which links back to the related user.

So if you're building a manual query, and want to involve the user id, you also need to join in commerce_customer_profile on commerce_customer_profile.profile_id = field_data_commerce_customer_address.entity_id. You'll then be able to use commerce_customer_profile.uid for further joins/constraints.


The customer addresses are linked to the user, see https://drupal.stackexchange.com/a/121356/2322 for more details on this, so you've got an order that references a customer profile and this references a user.

It doesn't show this part, but find a more complete description about the commerce entities in this documentation page: http://www.drupalcommerce.org/user-guide/products

enter image description here

In any case, I'd definitely use Entity Field Query for this, it will save you from digging in the database and it will give you a more consistent way to access your data.

  • In this case, I can't use Entity Field Query because of the number of joins I have to do. 8 joins in and I wish I could, though. There's a reason why you don't get Drupal Commerce data straight out of the database. Jul 10, 2014 at 22:27

It's a field table, so, in principle, any entity bundle could use use the field and store data to it. The entity_type column should give you the type of entity. If it is "node", then you need to join with the node table on nid, etc.

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.