Why do I get PDOException SQLSTATE[42S22] Column not found 1054 Unknown column 'tn.tid' in 'on clause'"?

Question

$rtid = arg(2);

$query = "SELECT node.nid AS nid FROM node
  WHERE (node.nid IN (
    SELECT tn.nid AS nid
    FROM taxonomy_index AS tn
    LEFT OUTER JOIN taxonomy_term_hierarchy AS th ON th.tid = tn.tid
    LEFT OUTER JOIN taxonomy_term_hierarchy AS th1 ON th.parent = th1.tid
    LEFT OUTER JOIN taxonomy_term_hierarchy AS th2 ON th1.parent = th2.tid
    WHERE (tn.tid = :rtid) OR (th1.tid = :rtid) OR (th2.tid = :rtid)
  ))
  AND (node.status = '1') AND (node.type IN ('product'))
  ORDER BY node_created DESC
  LIMIT 1 OFFSET 0";

$result = db_query($query, array(':rtid' => $rtid));

After I tried some times with the help of other users' answers, I got lost. This is the code I tried, but it gives me an error ("PDOException SQLSTATE[42S22] Column not found 1054 Unknown column 'tn.tid' in 'on clause'").

$query = db_select('node', 'n')
  ->condition('n.type', 'product')
  ->condition(
    db_or()
      ->condition('tn.tid',$rtid )
      ->condition('th1.tid',$rtid)
      ->condition('th2.tid',$rtid)
  )
  ->fields('n', array('nid'))     
  ->extend('PagerDefault')       
  ->limit(1);

$query->join('taxonomy_term_hierarchy', 'th', 'th.tid = tn.tid');
$query->join('taxonomy_term_hierarchy', 'th1', 'th.parent = th1.tid');
$query->join('taxonomy_term_hierarchy', 'th2', 'th1.parent = th2.tid');

$result = $query->execute();

Answer 1

The error you get is caused by the fact you don't use tn as alias of any database table.

The code I would write is the following one.

$tid = arg(2);
$query = db_select('node', 'n');

$tn = $query->join('taxonomy_index', 'ti', '%alias.nid = n.nid');
$th = $query->join('taxonomy_term_hierarchy', 'th', "%alias.tid = $tn.tid");
$th1 = $query->join('taxonomy_term_hierarchy', 'th1', "%alias.tid = $th.parent");
$th2 = $query->join('taxonomy_term_hierarchy', 'th2', "%alias.tid = $th1.parent");

$query->condition('n.type', 'product')
  ->condition(
    db_or()
      ->condition("$tn.tid", $tid)
      ->condition("$th1.parent", $tid)
      ->condition("$th2.parent", $tid)
  )
  ->fields('n', array('nid'))
  ->extend('PagerDefault')
  ->limit(1);

$query->join() is really doing an INNER join; if you need to make a LEFT OUTER join, then the code is the following.

$tid = arg(2);
$query = db_select('node', 'n');

$tn = $query->join('taxonomy_index', 'tn', '%alias.nid = n.nid');
$th = $query->addJoin('LEFT OUTER', 'taxonomy_term_hierarchy', 'th', "%alias.tid = $tn.tid");
$th1 = $query->addJoin('LEFT OUTER', 'taxonomy_term_hierarchy', 'th1', "%alias.tid = $th.parent");
$th2 = $query->addJoin('LEFT OUTER', 'taxonomy_term_hierarchy', 'th2', "%alias.tid = $th1.parent");

$query->condition('n.type', 'product')
  ->condition(
    db_or()
      ->condition("$tn.tid", $tid)
      ->condition("$th1.parent", $tid)
      ->condition("$th2.parent", $tid)
  )
  ->fields('n', array('nid'))
  ->extend('PagerDefault')
  ->limit(1);

As side notes:

• $query->join(), and $query->addJoin() returns the alias they used
• In the conditions passed to $query->join(), and $query->addJoin() %alias is replaced with the table alias the method is using

SelectQuery::addJoin() avoids conflicts between aliases using the following code.

  $alias_candidate = $alias;
  $count = 2;
  while (!empty($this->tables[$alias_candidate])) {
    $alias_candidate = $alias . '_' . $count++;
  }
  $alias = $alias_candidate;

  if (is_string($condition)) {
    $condition = str_replace('%alias', $alias, $condition);
  }

Writing code similar to the one I used, you are sure not to cause any conflict between aliases, and it is useful in the case the code is changed successively, or part of the query is populated from a function.
In this case, the join part could have written as follows, and it could still work correctly.

$tn = $query->join('taxonomy_index', 'tn', '%alias.nid = n.nid');
$th = $query->join('taxonomy_term_hierarchy', 'th', "%alias.tid = $tn.tid");
$th1 = $query->join('taxonomy_term_hierarchy', 'th', "%alias.tid = $th.parent");
$th2 = $query->join('taxonomy_term_hierarchy', 'th', "%alias.tid = $th1.parent");