Known values:

  • The nid of a node that has reference to more than one commerce_product
  • The tid from the field_TERMFIELD field attached to the commerce_product entity

commerce_product entity is referenced on the node entity via a multivalued entity reference field. When my known node has more than one referenced commerce product, I gotta figure out which one of those referenced products has a given tid.

A solution via either db_select() or EntityFieldQuery() are welcome.

2 Answers 2


I think I got it figured out:

$query = db_select('commerce_product', 'p');
$query->join('field_data_field_TERMFIELD', 't', 't.entity_id = p.product_id');
// This is the product reference field table. This field is attached to our node.
$query->join('field_data_field_product_reference', 'pr', 'pr.field_product_reference_product_id = p.product_id');

  // This is the product_id we are looking for.
  ->fields('p', array('product_id'))

  ->condition('field_TERMFIELD_tid', $tid)
  ->condition('pr.entity_type', 'node')
  ->condition('pr.entity_id', $nid);

 $result = $query->execute();

By joining the node and product fields tables that are required

$query = db_select('node', 'n');
$query -> leftJoin('field_data_field_product', 'seat', "seat.entity_id = n.nid AND seat.entity_type = 'node' AND seat.bundle = 'seat'");

In your case you need to inverse the process to get data from product table...

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.