Join query between term_node and node tables
Hi,
I have a drupal 6 powered website. I have a vocabulary called articles (with many terms). Every story node must have one term.
I am writing a module that will generate a list of last three updated article terms.
Example:
Sport [term]:
1 - node->title [story node]
2 - node->title [story node]
3 - node->title [story node]
Science [term]:
1 - node->title [story node]
2 - node->title [story node]
3 - node->title [story node]
Trips and vacations [term]:
1 - node->title [story node]
2 - node->title [story node]
3 - node->title [story node]
(the vocabulary actually contain much more terms but the module will be limited to 3 only)
The order of the terms is determined by the story nodes.
(From the example above, the last story was tagged as sport, the one before was science and the one before was trips and vacations etc...). If I publish a new story and tag it as Politics then the politics category will be displayed at the top (the other categories will drop one level down - the Trips and vacation won't be shown).
So what I tried to do is to get the latest 3 updated terms by using the following SQL query:
$sql = "SELECT DISTINCT(t.tid) FROM {term_node} as t, {node} as n WHERE t.nid=n.nid AND n.type='story' AND n.status='1' AND n.promote='1' ORDER BY n.created DESC LIMIT 3";I am getting three terms but the problem is that they are not ordered by the node creation time (I think they are actually order by using the term id).
My idea was that after I get the 3 terms id (correctly!) I will pick 3 nodes using:
taxonomy_select_nodes([term_id], 'or', 0, FALSE, 'n.created DESC');
Any help or suggestions regarding the above sql query is highly welcomed!
Thank you very much,
Fadi