By sethcohn on

Is there an easy way to generate a _clean_ list of taxonomy terms used in the most recently created nodes?

Playing with views, I could create a list of terms used, but not a list with duplicates removed, multiple terms split apart (ie no commas), etc.

I'm thinking along the lines of 'Recent Topics Discussed', so I don't want the cruft of doubles/etc, just a way for someone to see XYZ was discussed, and a single link (via taxonomy) to items using that term.

Seems simple enough, but for some reason I don't see an 'out of the box' way to generate it...

⋅ Categories: Drupal 5.x, Drupal 6.x

Comments

Christefano-oldaccount’s picture

Christefano-oldaccount commented

You can look at the Suggested Terms module for examples on getting recent terms:

      $query = "SELECT t.name AS name FROM {term_data} t INNER JOIN {term_node} n ON (t.tid = n.tid) WHERE t.vid = %d GROUP BY t.name ORDER BY t.tid desc";
sethcohn’s picture

sethcohn commented

At first, I didn't think that would work, thinking it's recently created and not recently used, but I think the join might make it work...

I'll follow up shortly.

sethcohn’s picture

sethcohn commented

Well, wasn't quite as easy as the above sql, but close enough.

The complexity: We want the greatest of the nids, otherwise if it returns an older node nid, we'll lose the fresher nids (and show more stale terms first). We also want no duplicates in terms, and the typical "group by" picks some nid, not the newest, unless we add the max select.

This gave me the results I want...

SELECT  t.name AS name , max(n.nid) AS node 
   FROM term_data t INNER JOIN term_node n ON t.tid = n.tid 
    WHERE t.vid = %d GROUP BY name ORDER BY node desc

Now, for bonus points, is there a better SQL query to give the same results?

summit’s picture

summit commented

Subscribing, looking for optimal sql also.

greetings,
Martijn

kaare’s picture

kaare
him/his
Primary language Norwegian Bokmål
Location Tromsø
commented

Here is a query that will select recent terms based on node update/change, and not based on 'nid' as the above query. I haven't tried this on a large data set, but I imagine it is rather slow as it has two joins.

select max(n.changed) changed, td.name
  from term_data td
    inner join term_node tn on td.tid = tn.tid
    inner join node n on tn.nid = n.nid
  group by td.tid
  order by changed desc, name