Strange PDOException Error With Computed Field

Question

I have a computed field which is calculating values based on an entity reference field. It works fine when calculating an AVG, but I realized today that what I actually would like is for it to find the most popular result in a column. So I altered the code as suggested here. However, now it's throwing me a PDOException error that (I think, I'm not exactly an expert) is related to the WHERE portion of the query. I'm not sure why it would be doing this. The code and error are included below. Any suggestions?

Old, working 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'] = "-10";
}

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(); 
}

New, error-throwing 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'] = "-10";
}

else {
    $entity_field[0]['value'] = db_query('
    SELECT COUNT(field_review_payment_value) AS `Rows`
    FROM (field_data_field_review_payment)
    GROUP BY (field_review_payment_value)
    ORDER BY `Rows` DESC
    LIMIT 1
    WHERE entity_id IN (:nids)',
    array(':nids' => $nids)
   )->fetchField(); 
}

Error:

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 'WHERE entity_id IN ('16', '17', '21', '22', '30', '31', '32', '33', '34', '35', ' at line 6: SELECT COUNT(field_review_payment_value) ASRowsFROM (field_data_field_review_payment) GROUP BY (field_review_payment_value) ORDER BYRowsDESC LIMIT 1 WHERE entity_id IN (:nids_0, :nids_1, :nids_2, :nids_3, :nids_4, :nids_5, :nids_6, :nids_7, :nids_8, :nids_9, :nids_10, :nids_11, :nids_12, :nids_13, :nids_14, :nids_15, :nids_16, :nids_17, :nids_18, :nids_19, :nids_20, :nids_21, :nids_22); Array ( [:nids_0] => 16 [:nids_1] => 17 [:nids_2] => 21 [:nids_3] => 22 [:nids_4] => 30 [:nids_5] => 31 [:nids_6] => 32 [:nids_7] => 33 [:nids_8] => 34 [:nids_9] => 35 [:nids_10] => 36 [:nids_11] => 38 [:nids_12] => 40 [:nids_13] => 45 [:nids_14] => 46 [:nids_15] => 47 [:nids_16] => 50 [:nids_17] => 51 [:nids_18] => 52 [:nids_19] => 57 [:nids_20] => 75 [:nids_21] => 79 [:nids_22] => 85 ) in eval() (line 26 of /Applications/MAMP/htdocs/sites/all/modules/computed_field/computed_field.module(466) : eval()'d code).

Answer 1

Try moving the WHERE clause to before the GROUP BY statement:

$entity_field[0]['value'] = db_query('
  SELECT COUNT(field_review_payment_value) AS `Rows`
  FROM (field_data_field_review_payment)
  WHERE entity_id IN (:nids)
  GROUP BY (field_review_payment_value)
  ORDER BY `Rows` DESC
  LIMIT 1',
  array(':nids' => $nids)
)->fetchField(); 

MySQL queries have to follow a certain format and the WHERE clause needs to be before GROUP BY, ORDER BY, LIMIT, etc.