Computed field won't update itself or save to the database

Question

UPDATED ISSUE: In the original issue below, I was getting an error when a query was empty. I've now gotten rid of the error, but content no longer saves into the database (where it saved fine before). Also, if the "store value to database" option is unchecked, then the data does not display at all. The new portion is the the second piece of the below code.

$entity_field[0]['value'] = "";$nids = db_query(' 
    SELECT entity_id FROM {field_data_field_review_venue_reference}
    WHERE field_review_venue_reference_target_id = :nid',
    array(':nid' => $entity->nid)
    )->fetchCol();

if (!empty($nids)) {
    $entity_field[0]['value'] = array_pop(array_pop(field_get_items($entity_type, $entity, 'field_venue_default_value')));
   }

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

endif;

If the array_pop portion is all that is in the computed field, then the field saves to the database (or displays, if the option is unchecked) just fine. If it's not included at all, the field saves/displays fine. However, when it is placed within if (!empty($nids)), no values for the field will save/display at all. This includes both the value of the array_pop and independent values calculated by SELECT AVG. As always, any help is greatly appreciated (and thanks to both Clive and Tenken for getting me get rid of the initial error).

ORIGINAL ISSUE: I have two content types: Review and Venue. Each review has an entity reference field to relate it back to the correct venue node. On the Venue nodes, I have computed fields to display calculated averages of fields from reviews referencing the current node (i.e. Average Payment Rating for Joe's Nasty Catering). For one of these fields, the code is:

$entity_field[0]['value'] = "";$nids = db_query('
  SELECT entity_id FROM {field_revision_field_review_venue_reference}
  WHERE field_review_venue_reference_target_id = :nid',
  array(':nid' => $entity->nid)
)->fetchCol();

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

The computed field works 100% correctly when a review has already been submitted for the current venue. However, when no reviews have been submitted, I get an error of PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064. It seems that this is because there are no entries where the Node Reference field is equal to the current NID.

Is there some way to fix this? I hadn't gone through the steps necessary to create the error until today, and as such I just realized that the site I'm building is essentially broken because of it.

Answer 1

If you would print the rest of the MySQL error message I assume your error is a malformed query such as:

SELECT AVG(field_review_payment_value) FROM {field_data_field_review_payment} WHERE entity_id IN ()

Eg, you cant have an empty IN primary key list.

Since you want to show an average value you could do something simple like:

if (isset($nids)) {
  $entity_field[0]['value'] = db_query('
    SELECT AVG(field_review_payment_value) FROM
    {field_data_field_review_payment}
    WHERE entity_id IN (:nids)',
    array(':nids' => $nids)
  )->fetchField();
} else {
  $entity_field[0]['value'] = 0; // no values to average == 0.
}