Drupal computed field code snippet for counting nodereferences – Addendum

When I first posted this, the sql query seemed to work fine, but after a bit of testing I noticed that node revisions will muck it. The query needed to get more complicated to look for the max vid instead of just relying on the nid. The symptom was that I would delete a nodereference, but it would still be counted (it would still count the previous revision’s value). The revised computed field value is below. Computed Field module asks us to add code snippets to the handbook pages, but they’re nowhere to be found after the D6 upgrade. http://drupal.org/node/126522Here’s one that I just created to calculate the number of nodereferences for a cck type.Background:ÊCCK typesVenueEvent (includes datestamp field and is node referenced to Venue)We wanted to create a view of Venues with upcoming events, including the number of events in parentheses. LIke so:Venue X (2)Venue Y (1)Created a computed field for Venue type, stored in db as int(10), and added the following code:THISÊISÊNOÊGOODÊWHENÊUSINGÊNODEÊREVISIONS:$node_field[0][‘value’] = db_result(db_query(“SELECT COUNT(DISTINCT cte.nid) FROM content_type_event cte INNER JOIN node n ON cte.nid = n.nid where cte.field_venue_nid = $node->nid AND n.status = 1 AND cte.field_datestamp_value >= UNIX_TIMESTAMP()”));WHENÊUSINGÊNODEÊREVISIONS, ITÊSHOULDÊLOOKÊLIKEÊTHIS (it’s a self join on the content type table to look at the latest revision):$node_field[0][‘value’] = db_result(db_query(“select count(distinct cte.nid) from (select nid, max(vid) as maxvid from content_type_event group by nid) as x inner join (content_type_event cte inner join node n on cte.nid= n.nid) on cte.nid = x.nid and cte.vid = x.maxvid where cte.field_venue_nid = $node->nid and n.status = 1 and cte.field_datestamp_value >= UNIX_TIMESTAMP()”));ÊCreated a view with filter computed field >= 0Hope this helps someone.

• Author: Darko Hrgovic