How to use condition with multiple term

Question

I try to get all the node that belong to 2 taxonomies terms (term 1 AND term 2).

I try with this :

    $select = db_select('node', 'n')
      ->fields('n', array('nid','title'))
      ->condition('n.status', 1);
    $select->fields('n', array('nid'));
    $select->fields('n', array('title'));
    $select->orderBy('n.title', 'ASC');
    $select->join('field_data_field_sous_theme', 'ss', 'ss.entity_id = n.nid');
    $select->condition(db_and()->condition('ss.field_sous_theme_tid', 517)->condition('ss.field_sous_theme_tid', 514));
    $results = $select->execute()->fetchCol();

or this :

$select = db_select('node', 'n')
  ->fields('n', array('nid','title'))
  ->condition('n.status', 1);
$select->fields('n', array('nid'));
$select->fields('n', array('title'));
$select->orderBy('n.title', 'ASC');
$select->join('field_data_field_sous_theme', 'ss', 'ss.entity_id = n.nid');
$select->condition(db_and()->condition('ss.field_sous_theme_tid', 517)->condition('ss.field_sous_theme_tid', 514));
$results = $select->execute()->fetchCol();

But there is no result.

And when i try this :

$select = db_select('node', 'n')
  ->fields('n', array('nid','title'))
  ->condition('n.status', 1);
$select->fields('n', array('nid'));
$select->fields('n', array('title'));
$select->orderBy('n.title', 'ASC');
$select->join('field_data_field_sous_theme', 'ss', 'ss.entity_id = n.nid');
$select->condition('ss.field_sous_theme_tid', array(517,514), 'IN');    
$results = $select->execute()->fetchCol();

I've all the nodes that belong to the term 1 OR the term 2.

I've you got an idea to solve my problem ?

Ps: sorry for my bad english, i'm french :-)

Answer 1

What about getting the arrays of nids for each of the tids you want to search with taxonomy_select_nodes($tid) and then intersect the lists with array_intersect. There should be a more Drupalistic way however...

Answer 2

Join your field_data_field_sous_theme table a second time using a different alias:

$select = db_select('node', 'n')
  ->fields('n', array('nid','title'))
  ->condition('n.status', 1);
$select->fields('n', array('nid'));
$select->fields('n', array('title'));
$select->orderBy('n.title', 'ASC');
$select->join('field_data_field_sous_theme', 'ss', 'ss.entity_id = n.nid');
$select->join('field_data_field_sous_theme', 'ss2', 'ss2.entity_id = n.nid');
$select->condition(db_and()->condition('ss.field_sous_theme_tid',517)->condition('ss2.field_sous_theme_tid', 514));
$results = $select->execute()->fetchCol();

This is how I would do it in strict SQL. I agree there might be a more Drupally way to do it, if you are willing to share the rest of the problem.