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!
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
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 Smith <autiger02-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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! > > -- > You received this message because you are subscribed to the Google Groups > "Ruby on Rails: Talk" group. > To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org > To unsubscribe from this group, send email to > rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org> > . > For more options, visit this group at > 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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Erol Fornoles
2010-Oct-15 04:21 UTC
Re: Re: How do I get this query to work in PostgreSQL?
On Fri, Oct 15, 2010 at 11:46 AM, Lee Smith <autiger02-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.