0

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).

0

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.

| improve this answer | |
  • 1
    To add to Clive's answer, when you run into SQL syntax errors, running the query in the MySQL command line or in PHPMyAdmin etc. will usually point you in the right direction in terms of debugging. Also the PHP in question does not conform to Drupal's recommended procedure when it comes to building queries. using the D7 database API might have mitigated this issue. – user7667 Jul 8 '12 at 21:48
  • That got rid of the error, but now it's not displaying or saving any values for the field Does anything about the structure of the COUNT part of the query look incorrect to you? @coleopterist - I'm not familiar with the Database API, as the advice I got when originally writing the field didn't include it. Do you have any suggestions on how to alter the query, or where to read up on using it instead of querying the database directly? – Mrweiner Jul 8 '12 at 22:19
  • Sorry about the last comment asking what was wrong...I had an extra apostraphe. I'm still curious about the Database API issue though. I know that there's documentation on Drupal.org, but any direction as to which part I need to be looking at would be awesome. I'm sure an API as large as that would be rather complex. – Mrweiner Jul 8 '12 at 22:49
  • For the types of queries you're running at the moment I think the Dynamic queries page is the best resource, it goes through all the options available. It's worth bearing in mind that you're already technically using the database API by using db_query, you're just not currently building a dynamic query. When to use either is a judgment call...db_query is a lot quicker, but db_select etc. let you build the queries dynamically which is good for some situations – Clive Jul 8 '12 at 23:28

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.