By NaSPA1 on

I'm trying to analyze the popularity of content on my site by node & taxonomy term. So far, the best query I've come up with is this:

select  n.nid, n.created, n.title, n.uid, nc.totalcount, u.name, tn.tid from node as n LEFT JOIN node_counter as nc using (nid) join users as u using (uid)  join term_node as tn on n.nid = tn.nid  join term_data as td on tn.tid = td.tid where n.type = 'naspa_articles' and td.vid = 5 order by nc.totalcount desc

The challenge is that I'm getting multiple rows for each node that has more than one category assigned in the vocabulary. What I'd like is to get 1 row with either the "lightest" or lowest numbered tid... Just cant seem to come up with the right query. At the moment, the intention is to use Excel to summarize the data into pretty reports for distribution, and not to craft a module per se out of this - so the query doesnt need to be super-fast.

Any suggestions on how to tweak the query to do this are greatly appreciated!

Regards,

Radi

⋅ Categories: Drupal 5.x

Comments

panis’s picture

panis commented

Step #1: Create temporary table - replace with the specific types:
create table tmp (nid int, tid int)

Step #2: grab the minimum or lightest tid and put into temp table:
insert into tmp select nid,min(tid) from term_node group by nid;

Step #3: Join with the tmp table to get the lightest tid instead of with the term_node table in your original query.

You know what to do here...

If you really wanted to condense all that into one query-- look at nested select calls if your database allows it - you could possibly nest the select call in step#2 into your final query replacing the term_node table with the query from step #2. But this may be hit your performance real hard depending on your database type and size.