On Mar 16, 1:22 am, Dido <[email protected]> wrote:
> I've been using the Sequel ORM for a little while and it seems like a
> really expressive system for doing database work. Once in a while,
> however, I need to make queries where I'm not selecting from the
> columns of the database directly, but rather a function of some
> columns, e.g. I want to do a count of the number of days for which
> records exist in a particular table, for which I would use the
> following raw SQL:
>
> select count(distinct date_trunc('day', date)) from my_table;
Currently, the best way to do this in 2.11.0 is:
DB[:my_table].select{|o| o.count("distinct ?".lit(o.date_trunc
('day', :date)))}
In the master branch, you can do:
Sequel.virtual_row_instance_eval = true
DB[:my_table].select{count("distinct ?".lit(date_trunc('day',
date)))}
The virtual_row_instance_eval = true will no longer be optional in
3.0.
> In my_table, the date column is a timestamp that each row has. The
> documentation available seems awfully sparse on how to do this, and so
> I'm reduced to using raw SQL to do this sort of thing, rather
> unpleasant, and somewhat defeats the purpose of the library itself. I
> can't seem to figure out how to replace the * in select * from ...
> with something else very easily. There don't seem to be any examples
> in the documentation on how to do this properly. The
> Sequel::Dataset#select method seems to want actual column names, and I
> can't seem to figure ouut how to use the block argument to it, as
> there don't seem to be any examples on how to use it or even what a
> SQL::VirtualRow class (which it yields) is supposed to do.
Documentation on the virtual row block feature is currently pretty
weak, and I apologize for that. I'm going to be reviewing all
documentation between now and 3.0, and I've added a todo list item to
add an RDoc page describing the virtual row block feature and how to
use it.
As for Dataset#select, you don't have to give it column names, it
takes any object that the dataset can literalize:
dataset.select(:column, 'string', 1000, Date.today){|o| [o.column,
o.function(1)]}
If you provide a block, it is merged with the arguments. If you want
the block to specify multiple columns, you need to have it return an
array.
> Here's another query:
>
> select date_trunc('day', date) as date_, count(*) from my_table group
> by date_
>
> to give the number of rows for each day in the table. Any advice on
> how to change the select clause of a select statement more generally?
DB[:my_table].select{|o| [o.date_trunc('day', :date).as(:date_),
o.count('*'.lit)]}.group(:date_)
Jeremy
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
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
-~----------~----~----~----~------~----~------~--~---
