By pamphile on

i need a frontpage SQL snippet that can list all nodes within specific taxonomy terms.

I tried using this SQL code but it does not work:

$thistermroot = "1,4,3,6,7";

$sql = SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM {node} n INNER JOIN  {users} u ON u.uid = n.uid INNER JOIN {node_revisions} r ON r.vid = n.vid WHERE  {term_node}.tid in ($thistermroot)";

This works but it does not fetch the node body

$thistermroot = "1,4,3,6,7";

$sql = "SELECT * FROM {node} INNER JOIN {term_node} ON {node}.nid = {term_node}.nid 
WHERE {term_node}.tid in ($thistermroot) ORDER BY {node}.created ";

Help ! Any ideas ?

Marcel

http://www.bigvertiser.com

⋅ Categories: Drupal 4.7.x

Comments

ardas’s picture

ardas commented

The reason of the error is the following:
you are trying to do WHERE {term_node}.tid but didn't include term_node table in the FROM section and didn't join it with node table.

The proper SQL will be as follows:
$sql = SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM {node} n INNER JOIN {users} u ON u.uid = n.uid INNER JOIN {node_revisions} r ON r.vid = n.vid INNER JOIN {term_node} tn on tn.nid = n.nid WHERE tn.tid in ($thistermroot)";