Blog / 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.

Here's one that I just created to calculate the number of nodereferences for a cck type.


CCK types
Event (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:


$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 >= 0

Hope this helps someone.


  • Thanks

    Thank you , very helpful.

  • Yes.....

    I found this out the hard way with node revisions.

  • Thanks for the snippet, I am

    Thanks for the snippet, I am trying to do something very similar and feel I am very close! However, I am slightly confused about something. Am I right in saying that the code in the computed field's 'Comoputed Code:' box (i.e the bit that does $node_field[0]['value'] = ...) Is only called when the containing node is saved and not every time the field is queried? That's what I seem to be seing on my site. If this is the case won't the number of events (in your example) only update whenever the venu node is edited and saved and not automatically when new events are created? Hope I am missing something!

  • Computed field auto update

    Yes, that's an issue with computed field. Much has been written about it on, including creating a cron hook in a module to update the field at regular intervals.


Leave a comment

(If you're a human, don't change the following field)
Your first name.
(If you're a human, don't change the following field)
Your first name.
(If you're a human, don't change the following field)
Your first name.

Sign up for updates