0
$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();
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");
  • i put your query code in, but there is no any result output. i print the query. SELECT n.nid AS nid FROM node AS n INNER JOIN taxonomy_index AS ti ON ti.nid = n.nid INNER JOIN taxonomy_term_hierarchy AS th ON th.tid = ti.tid INNER JOIN taxonomy_term_hierarchy AS th1 ON th1.tid = th.parent INNER JOIN taxonomy_term_hierarchy AS th2 ON th2.tid = th1.parent WHERE (n.type = product) AND( (ti.tid = 6) OR (th1.parent = 6) OR (th2.parent = 6) ) then test term id=6 run in phpmyadmin, shows 1054 - Unknown column 'product' in 'where clause' – stackoverflow002 Dec 8 '12 at 5:40
  • the second ways with term id =4 test show the same result, 1054 - Unknown column 'product' in 'where clause' SELECT n.nid AS nid FROM node AS n INNER JOIN taxonomy_index AS tn ON tn.nid = n.nid LEFT OUTER JOIN taxonomy_term_hierarchy AS th ON th.tid = tn.tid LEFT OUTER JOIN taxonomy_term_hierarchy AS th1 ON th1.tid = th.parent LEFT OUTER JOIN taxonomy_term_hierarchy AS th2 ON th2.tid = th1.parent WHERE (n.type = product) AND( (tn.tid = 4) OR (th1.parent = 4) OR (th2.parent = 4) ) – stackoverflow002 Dec 8 '12 at 5:45
  • after read you code again and again, i don't know where is the error is?thank you – stackoverflow002 Dec 8 '12 at 6:07
  • The error is simply the fact there isn't any database table using tn as alias in db_select(), or in $query->join(). The error is telling you "I don't know what table is tn." – kiamlaluno Dec 8 '12 at 6:49
  • taxonomy_index AS tn, but in your code. $tn = $query->join('taxonomy_index', 'tn', '%alias.nid = n.nid'); this line shows tn as alias to the taxonomy_index – stackoverflow002 Dec 8 '12 at 7:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.