How to select FROM {commerce_product} p WHERE p.field_TERMFIELD = $given_tid and the resulted p.product_id is referenced on a given nid 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.
I think I got it figured out:

$query = db_select('commerce_product', 'p');
$query-&gt;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-&gt;join('field_data_field_product_reference', 'pr', 'pr.field_product_reference_product_id = p.product_id');

$query
  // This is the product_id we are looking for.
  -&gt;fields('p', array('product_id'))

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

 $result = $query-&gt;execute();