Now everything is working, at least for PostgreSQL. Thank you very much for
your help.
Both group_and_count{ date(created_at) } and group_and_count{
created_at.cast(Date) } work with PostgreSQL.
The output is as follows:
{:date=>#<Date: 2012-06-21 (4912199/2,0,2299161)>, :count=>2}] #
using date(created_at)
{:created_at=>#<Date: 2012-06-21 (4912199/2,0,2299161)>, :count=>2}] #
using created_at.cast(Date)
I updated the gist <# Sequel Google Groups # Topic:
https://groups.google.com/forum/?fromgroups#!topic/sequel-talk/WlgsyLtd9eE
require 'sequel' require 'logger' require 'date' # Connection #
============= DB = Sequel.sqlite # DB =
Sequel.connect('postgres://postgres:password@localhost/tinyclone') # Schema
# ============= DB.create_table? :links do String :short, :primary_key =>
true, :size => 15 DateTime :created_at, :null => false end DB.create_table?
:urls do primary_key :id String :original, :size => 255, :unique => true,
:null => false foreign_key :link_short, :links, :type => String, :on_delete
=> :cascade end DB.create_table? :visits do primary_key :id inet :ip, :null
=> false String :country, :size => 255 DateTime :created_at, :null => false
foreign_key :link_short, :links, :type => String, :on_delete => :cascade
end # Associations # ============= class Url < Sequel::Model many_to_one
:link, :key => :link_short end class Link < Sequel::Model one_to_one :url,
:key => :link_short one_to_many :visits, :key => :link_short end class
Visit < Sequel::Model many_to_one :link, :key => :link_short end
Link.unrestrict_primary_key # Inserting data # ============= link1 =
Link.create(:short => 'example', :created_at => Time.now) link1.url =
Url.new(:original => 'http://www.example.com') link2 = Link.create(:short
=> 'google', :created_at => Time.now) link2.url = Url.new(:original =>
'http://www.google.com') visit1 = Visit.create(:ip => '23.34.56.43',
:country => 'China', :created_at => Time.now) visit2 = Visit.create(:ip =>
'23.34.56.44', :country => 'Germany', :created_at => Time.now) visit3 =
Visit.create(:ip => '23.34.56.46', :country => 'Germany', :created_at =>
Time.now) link1.add_visit(visit1) link1.add_visit(visit2)
link2.add_visit(visit3) # Querying # ============= short = 'example'
number_of_days = 10 main_query1 = DB[:visits].group_and_count{
date(created_at) }.filter(:link_short => short) main_query2 =
DB[:visits].group_and_count{ created_at.cast(Date) }.filter(:link_short =>
short) query1 = main_query1.filter(:created_at => (Date.today -
number_of_days) .. (Date.today + 1)) query2 =
main_query2.filter(:created_at => (Date.today - number_of_days) ..
(Date.today + 1)) p query1 p query1.all p query2 p query2.all # SQLite #
#<Sequel::SQLite::Dataset: # "SELECT date(`created_at`), count(*) AS
'count' # FROM `visits` WHERE ((`link_short` = 'example') AND #
(`created_at` >= '2012-06-11') AND (`created_at` <= '2012-06-22')) # GROUP
BY date(`created_at`)"> # => [{:"date(`created_at`)"=>nil, :count=>2}] #
#<Sequel::SQLite::Dataset: # "SELECT CAST(`created_at` AS date), count(*)
AS 'count' # FROM `visits` WHERE ((`link_short` = 'example') AND #
(`created_at` >= '2012-06-11') AND (`created_at` <= '2012-06-22')) # GROUP
BY CAST(`created_at` AS date)"> # = [{:"CAST(`created_at` AS date)"=>2012,
:count=>2}] # Postgres # #<Sequel::Postgres::Dataset: # "SELECT
date(\"created_at\"), count(*) AS \"count\" # FROM \"visits\" WHERE
((\"link_short\" = 'example') AND # (\"created_at\" >= '2012-06-11') AND
(\"created_at\" <= '2012-06-22')) # GROUP BY date(\"created_at\")"> # =>
[{:date=>#<Date: 2012-06-21 (4912199/2,0,2299161)>, :count=>2}] #
#<Sequel::Postgres::Dataset: # "SELECT CAST(\"created_at\" AS date),
count(*) AS \"count\" # FROM \"visits\" WHERE ((\"link_short\" = 'example')
AND # (\"created_at\" >= '2012-06-11') AND (\"created_at\" <=
'2012-06-22')) # GROUP BY CAST(\"created_at\" AS date)"> # =>
[{:created_at=>#<Date: 2012-06-21 (4912199/2,0,2299161)>, :count=>2}] if
DB.database_type == :postgres query3 = main_query2.filter{(created_at >=
(Sequel::CURRENT_DATE - number_of_days)) & (created_at <=
(Sequel::CURRENT_DATE + 1))} else query3 = main_query1.filter{(created_at
>= date(Sequel::CURRENT_DATE, "'-? days'".lit(number_of_days.to_i))) &
(created_at <= date(Sequel::CURRENT_DATE,'+1 day'))} end # During Testing:
DB.drop_table(:urls, :visits, :links)> with my test code and also added the
logging output when running the problem with postgres and sqlite.
Thanks again for your help!
Best regards,
Stefan
On Thursday, June 21, 2012 12:14:22 PM UTC+8, Jeremy Evans wrote:
>
> On Wednesday, June 20, 2012 8:52:28 PM UTC-7, Stefan Rohlfing wrote:
>
>> Thanks for your corrections and recommendations. I already updated the
>> gist <https://gist.github.com/2958805> accordingly.
>>
>> Now there is only one problem left: I want the rows to be grouped on the
>> date only, not the date + the exact time.
>> So, for example, with the current query I get the following output from
>> my test program:
>>
>> [{:created_at=>2012-06-21 10:45:18 +0800, :count=>1},
>> {:created_at=>2012-06-21 10:45:18 +0800, :count=>1}]
>>
>> However, what I need is this output:
>>
>> {:created_at=>2012-06-21, :count=>2}]
>>
>> In the original SQL query the rows are grouped according to the return
>> value of the 'date' function:
>>
>> SELECT date(created_at) as date, count(*) as count
>> FROM visits
>> WHERE link_short = '#{short}' and
>> created_at between CURRENT_DATE-#{nunber_of_days} and
>> CURRENT_DATE+1
>> GROUP BY date(created_at)
>>
>> Is there a way to accomplish the same in Sequel in a more or less
>> database-agnostic way?
>>
>>
> For SQLite, you can do:
>
> select_group{date(created_at)}
>
> For other databases, you should probably do:
>
> select_group{created_at.cast(Date)}
>
> The reason that doesn't work on SQLite is that SQLite doesn't have native
> date or time types. I might change Sequel so that casting to Date uses the
> date(function), since currently Sequel accepts the cast to date, but treats
> it as a cast to integer (which if you've read the SQLite documentation, is
> their expected behavior).
>
> Jeremy
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/HDAM0buuJo8J.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
