By SteveTurnbull-1 on

It's mysql question but specific to Drupal. Let's say I have a node plus its revisions:

vid___nid___field1___field2
55___10______x______a
56___10______x______b
57___10______x______c

I search for nodes where field2=b so I get the one with vid=56 (and nid=10), but at this point I'm only fetching NIDs. At a later point I fetch the node using node_load(10) ... and I get the one with vid=57 and field2 = 'c'.

But really I don't want either, I only want records with the highest vid of any given nid. (So in this example I'd get nothing, the highest vid of nid=10 has field2 of 'c').

I'm sure this is simple but I just can't see it.

Hope you can help.

Steve

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

Comments

nevets’s picture

nevets commented

If your version of mysql supports sub queries this should help

SELECT * FROM fun WHERE vid IN (SELECT MAX(vid) as max_vid FROM fun f  GROUP BY nid) AND field2 = 'blue'

Note you need to replace the table name (fun) twice with your actual table name (once in query, once in sub-query)

SteveTurnbull-1’s picture

SteveTurnbull-1 commented

Thanks for looking at that, in fact I forgot to answer my own question which was the solution you suggest.