Retrieve nodes sharing multiple taxonomy terms

Question

Query Drupal 7 for nodes sharing both taxonomy terms(tid). I am querying the taxonomy_index tid and nid (and sorting it by created column). The taxonomy_index table has a column for tid and the corresponding nid tagged with that tid.

I have tried multiple combinations but all of them seem to either return all nodes tagged with either tid1 or tid2, or simply just tid1. Some of my code look like:

<!-- Query the taxonomy_index -->
$results = db_select('taxonomy_index', 't')
  ->fields('t', array('nid', 'tid'))
  ->distinct()
  ->condition('t.tid', $tids, 'IN')
  ->orderBy('t.created', 'DESC')
  ->extend('PagerDefault')
  ->limit(9)
  ->execute() 
  ->fetchCol();

<!-- Build the pager -->
$total = db_select('taxonomy_index', 't')
  ->fields('t', array('nid', 'tid'))
  ->distinct()
  ->condition('t.tid', $tids, 'IN')
  ->countQuery()
  ->execute() // Execute the query
  ->fetchCol(); // Fetch column
$total_pager = $total[0];

pager_default_initialize($total_pager,9);

I have also tried adding:

$query->where('t.tid = 115 AND t.tid = 210'); // Query for specific tids= 115 and 210.

Anybody know a solution to this? Or is there a preferred alternative like building up a new index table or perhaps make a new tid in another vocabulary that contains both (tid1 + tid2 = tid3)?

Hey you should accept that awesome answer down there. It helped me a lot! Nice question, too! — leymannx

idFlood idFlood · Accepted Answer · 2014-08-20T14:46:17

You are right, the first example you give should return nodes which have the first or the second terms. The solution if you only want to filter on 2 terms is to do the query with a join.

In summary it should do a "first query" with the first term, then join on the same table with the nids from the first result and then filter on the second term.

$results = db_select('taxonomy_index', 't')
  ->fields('t', array('nid', 'tid'))
  ->condition('t.tid', $tid1); // Only take nodes with the first term.
// Now the result only contains nodes with one unique term ($tid1).
// We need all terms associated with the result nodes so we will make a left join.

// leftJoin doesn't return the query object so we can't chain it.
$results->leftJoin('taxonomy_index', 't2', 't.nid = t2.nid');
$results = $results->condition('t2.tid', $tid2)
  ->distinct()
  ->execute()
  ->fetchCol();

To explain it a little further imagine this data:

[{nid: 1, tid: 42}, {nid: 1, tid: 2014}, {nid: 2, tid: 42}, {nid: 3, tid: 1337}]

The first ->condition('t.tid', 42) will give this:

[{nid: 1, tid: 42}, {nid: 2, tid: 42}]

So now you can't check on this result for other $tid, that is why we do a join on 't.nid = t2.nid'. It will give back all the info about the result of the first condition:

[{nid: 1, tid: 42}, {nid: 1, tid: 2014}, {nid: 2, tid: 42}]

Now we can finally add the second condition $results->condition('t2.tid', 2014) and it should return this:

[{nid: 1, tid: 2014}]

Retrieve nodes sharing multiple taxonomy terms

2 Answers