saadlulu's answer is also correct, but since it doesn't specifically address the topic of taxonomy terms, I want to post my solution which I got working just a few minutes ago and explain what is happening so that some other newbie who comes along can understand what is happening well enough to alter it for their own uses.
My nodes are of type photo_gallery
and they have a taxonomy terms field called field_tags
and I want to grab all of the nodes which have the term for $term_id
$query = db_select('node', 'n');
$query->join('field_data_field_tags', 't', 't.entity_id=n.nid');
$result = $query
->fields('n', array('nid', 'title', 'created'))
->fields('t', array('entity_type', 'bundle', 'field_tags_tid', 'entity_id'))
->condition('entity_type', 'node')
->condition('bundle', 'photo_gallery')
->condition('field_tags_tid', $term_id)
->condition('status', 1)
->condition('type', 'photo_gallery')
->orderBy('created', 'DESC')
->execute();
The part I didn't know/understand was how the tables were structured behind the scenes for things like a term reference. When I looked into the actual SQL tables, then it became clear. The connections between a node and a taxonomy term are stored in a separate table and knowing which table that is was one of the big questions.
So, my field_tags
data is stored in the field_data_field_tags
table. I grabbed the nid of one of my nodes to see what the data looked like.
mysql> select * from field_data_field_tags where entity_id=1082;
+-------------+---------------+---------+-----------+-------------+----------+-------+----------------+
| entity_type | bundle | deleted | entity_id | revision_id | language | delta | field_tags_tid |
+-------------+---------------+---------+-----------+-------------+----------+-------+----------------+
| node | photo_gallery | 0 | 1082 | 1082 | und | 0 | 5 |
+-------------+---------------+---------+-----------+-------------+----------+-------+----------------+
1 row in set (0.00 sec)
Once I understood the info above, then I could see that I needed to join to the field_data_field_tags
table and do that join on entity_id=nid
.