mysql error on count of related records / Help Please!

mysql error on count of related records

Hello - I'm getting the following error when trying to get a precount of related records using the flourish ORM: 

MySQL error (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 ' entrants.survey_id) AS __flourish_count, surveys.id AS __flourish_column  FROM ' at line 1) in SELECT count(entrants.id, entrants.survey_id) AS __flourish_count, surveys.id AS __flourish_column  FROM entrants LEFT JOIN surveys ON entrants.survey_id = surveys.id  WHERE surveys.id IN (1) GROUP BY surveys.id ORDER BY surveys.id ASC

It looks like mysql doesn't like that comma in count(). Any ideas?

mysql version: 5.0.75-0ubuntu10.2 php version: PHP/5.2.6-3ubuntu4.2 with Suhosin-Patch

Thanks for any help you can give here!

Reply Quote
vena
10/12/09 18:04:35
  • Message #480

    Vena,

    You shouldn't ever need count(A,B). I don't think it is allowable SQL anyway. Your query should work just fine as : 

    SELECT count(*) AS flourish_count, surveys.id AS flourish_column FROM entrants
LEFT JOIN surveys ON entrants.survey_id = surveys.id 
WHERE surveys.id IN (1) 
GROUP BY surveys.id 
ORDER BY surveys.id ASC
    Reply Quote
    justbn
    10/13/09 14:08:24
    • Message #481

      The issue is that the ORM is generating that SQL. In Postgres (which I use daily) it is valid syntax, so that's why it is there.

      I also believe that changing it to count(*) should fix it, but I would like to write a unit test or two to make sure.

      Reply Quote
      wbond
      10/13/09 14:24:25
      • Message #482

        Yup, the ORM is generating it. Would it hurt performance to count(*) rather than an index?

        *edit: to answer my own question, apparently not. explain query shows it uses the two indexed keys for the count. i've changed line 1329 in fRecordSet::precount() to use count(*) in the meantime. if i run into any issues, i'll be sure to post them :)

        Reply Quote
        vena
        10/13/09 14:36:40
  • Message #497

    This issue was fixed in r720

    Reply Quote
    wbond
    10/22/09 16:23:17