1

EDITED: This has been altered to include the code suggested by Dooshta.

I need to fill a computed field with the average value of all entries in a certain field, and only from nodes that have a specific node reference field.

What I have right now in my Computed Field is:

$nids = db_query('
  SELECT entity_id FROM {field_revision_field_venue_reference_review}
  WHERE field_venue_reference_review_target_id = :nid',
  array(':nid' => $entity->nid)
)->fetchCol();

$entity_field[0]['value'] = db_query('
   SELECT AVG(field_payment_review_value) FROM {field_data_field_payment_review}
   WHERE entity_id IN (:nids)', 
   array(':nids' => $nids)
)->fetchField();

This is working, and displays the correct average from the correct nodes. However, I get a PODexception error on some pages that do not include the above code. This is confusing to me, because it seems that if there is bad syntax, then I should get the error on the page using the code as well as pages not using the code, right? Either that or it just shouldn't work.

PODexception Error

As requested by Booshta, here are screenshots of the tables I'm querying. The first is the table containing the entity reference field in the $nids part of the query Screenshot of Table field_data_field_venue_reference_review

The second is the table containg the values being averaged in the $entity_field part of the query Screenshot of Table field_data_field_payment_review

2
  • It looks like you might be using Drupal 6 code on a Drupal 7 site...can you confirm which version you're using? – Clive May 10 '12 at 0:36
  • Yes, I am using D7. I believe that the code on the page I linked to is for D6, but I thought that I had changed the required portions. If the displayed code is for D6, then that would explain my issues. – Mrweiner May 10 '12 at 0:55
1

I'm not familiar with the computed field module and not sure I completely understand the relationships between your nodes and fields, but here's about how you should be able to get things working.

The first query seems like it would be alright if you'd remove that extra closing bracket and used fetchField() to retrieve the value:

$entity_field[0]['value'] = db_query('
   SELECT AVG(field_payment_review_value) FROM {field_data_field_payment_review}
')->fetchField();

The second one needs some more work. I'm assuming that you have review nodes with the ratings and every review node is linked to a business node with a reference field of some sort.

First I would get the nid-s of those review nodes that are linked to the current business node as an array (using fetchCol()) with something like this:

$nids = db_query('
  SELECT entity_id FROM {field_data_field_business_reference}
  WHERE field_business_reference = :nid AND entity_type = :type AND bundle = :bundle',
  array(':nid' => $entity->nid, ':type' => 'node', ':bundle' => 'review')
)->fetchCol();

Then get the average:

$entity_field[0]['value'] = db_query('
   SELECT AVG(field_payment_review_value) FROM {field_data_field_payment_review}
   WHERE entity_id IN (:nids) AND entity_type = :type AND bundle = :bundle',
   array(':nids' => $nids, ':type' => 'node', ':bundle' => 'review')
)->fetchField();

UPDATE:

To avoid the error you're getting you can try wrapping the code inside some if statements to make sure it won't be executed when necessary criteria is not met:

$node = menu_get_object();
if (isset($node->type) && $node->type == 'venue') {
  // put the query that retrieves the nids here.
  if (is_array($nids) && !empty($nids)) {
    // put the query that calculates the average here.
  }
}

If that's too restrictive you might get away with just this much:

  // put the query that retrieves the nids here.
  if (is_array($nids) && !empty($nids)) {
    // put the query that calculates the average here.
  }
7
  • My node/field relationships: Node type - review: used as a form for users to rate the venues, linked to venues with entity reference field. Node type - venue : displays venue information and average of ratings results using computed fields. Thanks for the suggestions. Looks like not having the fetchField is what was giving me problems. Without it, I was getting PODexception error. I'll take a look at the second half later when I get a chance. If it works, which I'm sure it will, this is going to be selected as the answer. – Mrweiner May 12 '12 at 23:24
  • I got your code to work when removing references to entity_type, type, and bundle. But I then get this error other pages: "PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1: SELECT AVG(field_payment_review_value) FROM {field_data_field_payment_review} WHERE entity_id IN (); Array ( ) in eval() (line 11 of /Applications/MAMP/htdocs/drupal-7.12/sites/all/modules/computed_field/computed_field.module(466) : eval()'d code). – Mrweiner May 13 '12 at 1:35
  • It looks like you may have broken the query syntax. Make sure all the commas, quotes, brackets are still there and in correct locations. Also check that the table and field names used in the queries match with what you got in the database. If you don't get them working you could maybe update your question with images of the relevant database tables where necessary field names are shown and the queries you've put together to retrieve the data to make it easier to track down the problem(s). – Madis May 13 '12 at 10:53
  • I've edited/added my queries and screenshots of the PODexception and the relevant database tables. – Mrweiner May 13 '12 at 20:22
  • I misunderstood the problem (sorry about that), but your update helped to clear things up. See if the added bits in my answer help you to resolve the issue. – Madis May 13 '12 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.