0

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.

1 Answer 1

2

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.
}
5
  • In trying to post a comment, I think I accidentally edited out a suggestions to do if (!empty($nids)) { $entity_field[0]['value'] = 0; // no values to average == 0. }; endif; instead of if(isset and else. This is what I ended up doing. So, thank you both.
    – Mrweiner
    Jun 29, 2012 at 23:14
  • After adding this code into all the necessary fields the error was resolved, but for some reason the value of 0, as dictated by the last part of code, isn't being saved into the database. Any idea why that would be? I've posted my updated code in the issue.
    – Mrweiner
    Jun 29, 2012 at 23:56
  • @Mrweiner You probably need the language part of the field array as well, something like $entity_field[LANGUAGE_NONE][0]['value'] = 0;
    – Clive
    Jun 30, 2012 at 0:07
  • @Clive I've also noticed something else interesting. Adding that additional code above (not from this comment, but from what fixed the errors) also stops the value from updating when a new review is submitted. For example, there's a venue with 22 reviews and a field which calculates how many reviews have been submitted. The field now remains at 22 even after more reviews are submitted, until I remove the new code from the field, after which it updates itself. I'm assuming that that is more of the cause than the language issue, since it seems to affect any saving to the database of the field.
    – Mrweiner
    Jun 30, 2012 at 0:22
  • Solved it. Apparently the whole issue was that I had endif at the end, which apparently is only necessary if using colons instead of brackets. That's what I get for toying around with PHP when I don't really know PHP.
    – Mrweiner
    Jul 3, 2012 at 20:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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