db_select() which selects nodes which have a certain term

Question

I feel like this should be a simple question and therefore have been answered before, but all of the other Stackexchange answers I find doesn't seem to directly answer the question (at least as far as I can understand).

$query = db_select('node', 'n')
      ->fields('n', array('nid', 'title', 'created'))
      ->condition('status', 1)
      ->condition('type', 'photo_gallery')
      ->orderBy('created', 'DESC')
      ->execute();

This is my db_select() to fetch a list of nodes.

My photo_gallery content type also has a term reference field named field_tags which references the tags taxonomy.

How do I select a list of nodes from the db and only get nodes which have a particular tag?

Answer 1

You have to use the Join statement; what you basically need to do is joining your query with another from another table.

Here is an example that you can use.

$query = db_select('file_managed', 'fm');
$query->join('field_data_field_image', 'fi', 'fm.fid = fi.field_image_fid');
$query->groupBy('fm.fid');
$query->fields('fm', array('filename', 'uri'));
$query->fields('fi', array('field_image_width', 'field_image_height'));
$query->condition('fi.bundle','image_for_slider','=');
$result = $query->execute()->fetchAssoc();

In this example, the file_managed table is joined with field_data_field_image; you have to switch those with node and field_tags.

Answer 2

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.