Sorting cck fields in drupal6 views by 'revision date'

Question

I'm looking for a way to construct a view in drupal6 by letting it grab the 'added date' from a nodereference cck field which has multiple values.

What I need is a list of titles of referenced nodes, sorted (desc) by the date they were added to the reference-list.

I've tried setting up a 'node' view, letting it grab a node ID from the url and letting it display node titles that are linked by a relationship to the cck field. When sorting it by 'revision date' or 'revision vid' I obviously got the "normal" list, since all entries were present in the latest revision.

Any ideas on how to tackle this?

Answer 1

I'm not sure this is actually possible in SQL the way I'm understanding it. If I'm understanding correctly you want the revision date of the attached nodes but these nodes are being joined on through a relationship which will make their revision_date look something like attached_node.revision_date and attached_node_2.revision_date. So sorting by revision date would be ambiguous but you can't sort on the same column from two different tables.

So you may have to redefine the goal, or if you can figure out the SQL (maybe I'm just filled with turkey and slow today) but views can't handle it you can use hook_views_query_alter in your module and adjust $query->where iirc. You can also hook_views_pre_execute though this is distinctly uglier and requires that you be able to parse the SQL. It's nice if you're just going to replace the SQL entirely though on some view and only use the views ui for its wonderful argument and outputting options.