Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Database agnostic query? #10

Open
ghost opened this Issue Jan 22, 2011 · 1 comment

Comments

Projects
None yet
1 participant
@ghost

ghost commented Jan 22, 2011

It would be nice to have these two queries work in all three db flavors (MySQL, SQLite and Postgresql). In the meantime, here are the queries that work for the associated databases:

Postgresql

Ticket.timeline_opened_tickets:
self.count(:group => 'date(created_at)', :having => ['date(created_at) >= ? and date(created_at) <= ?', (Time.zone.now.beginning_of_day - 30.days).to_date.to_s, (Time.zone.now.end_of_day - 1.day).to_date.to_s])

Ticket.timeline_closed_tickets:
self.count(:group => 'date(closed_at)', :having => ['date(closed_at) >= ? and date(closed_at) <= ?', (Time.zone.now.beginning_of_day - 30.days).to_date.to_s, (Time.zone.now.end_of_day - 1.day).to_date.to_s])

MySQL & SQLite

Ticket.timeline_opened_tickets:
self.count(:group => 'date(created_at)', :having => ['date_created_at >= ? and date_created_at <= ?', (Time.zone.now.beginning_of_day - 30.days).to_date.to_s, (Time.zone.now.end_of_day - 1.day).to_date.to_s])

Ticket.timeline_closed_tickets:
self.count(:group => 'date(closed_at)', :having => ['date_closed_at >= ? and date_closed_at <= ?', (Time.zone.now.beginning_of_day - 30.days).to_date.to_s, (Time.zone.now.end_of_day - 1.day).to_date.to_s])

Could you detect the type of Database Adapter being used and use alternate queries?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment