How do I get this query to work in PostgreSQL?

Query:
Ticket.count(:group => ‘date(created_at)’, :having =>
[‘date_created_at >= ? and date_created_at <= ?’, “2010-09-14”,
“2010-10-13”])

Successful MySQL 5.1.41 output:

SELECT count(*) AS count_all, date(created_at) AS date_created_at FROM
tickets GROUP BY date(created_at) HAVING date_created_at >=
‘2010-09-14’ and date_created_at <= ‘2010-10-13’

PostgreSQL 8.4.5 error:

ActiveRecord::StatementInvalid (PGError: ERROR: column
“date_created_at” does not exist
LINE 1: …FROM “tickets” GROUP BY date(created_at) HAVING
date_creat…
^
: SELECT count(*) AS count_all, date(created_at) AS date_created_at
FROM “tickets” GROUP BY date(created_at) HAVING date_created_at >=
‘2010-09-14’ and date_created_at <= ‘2010-10-13’ )

Many thanks for the help!

Try:

Ticket.count(:group => ‘date(created_at)’, :having =>
[‘date(created_at) >= ? and date(created_at) <= ?’, “2010-09-14”,
“2010-10-13”])

HTH

On Fri, Oct 15, 2010 at 11:20 AM, Lee S. [email protected] wrote:

http://groups.google.com/group/rubyonrails-talk?hl=en.


Erol M. Fornoles
http://github.com/Erol
http://twitter.com/erolfornoles
http://ph.linkedin.com/in/erolfornoles

Erol, that worked for PostgreSQL. However, it fails in MySQL:

Mysql::Error: Unknown column ‘created_at’ in ‘having clause’: SELECT
count(*) AS count_all, date(created_at) AS date_created_at FROM
tickets GROUP BY date(created_at) HAVING date(created_at) >=
‘2010-09-14’ and date(created_at) <= ‘2010-10-13’

Is this just one of those queries that simply cannot be database
agnostic?

Thanks for the help.

On Fri, Oct 15, 2010 at 11:46 AM, Lee S. [email protected] wrote:

Is this just one of those queries that simply cannot be database
agnostic?

AFAIK yes.


Erol M. Fornoles
http://github.com/Erol
http://twitter.com/erolfornoles
http://ph.linkedin.com/in/erolfornoles

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs