Here's my table definition and the SQL I am running against it:

class Order < Sequel::Model(:orders)
set_schema do
   primary_key :id, :auto_increment => true
   varchar :market, :size => 12
   decimal :created_at, :elements => [15,5]
   decimal :ack, :elements => [15,5]
end
end

select market, minute(from_unixtime(ack)) AS minute, round(avg((ack -  
created_at) * 1000), 2) AS ack_average from orders  where 'ack' >  
(UNIX_TIMESTAMP(now()) - (15 * 60) and 'market' = 'ICE' group by  
minute(from_unixtime(ack));

As you may infer, the ack and created_at columns are large floating  
point numbers (seconds plus microseconds) representing time relative  
to the UNIX epoch. Is it possible to write this in Sequel and have it  
generate the correct SQL? Here's an attempt...

market = 'ICE'
Order.filter{:market => market && :ack >  
15.minutes.ago}.avg(:no_idea_what_to_do_from_here).group(:still_lost)

I need to do math (subtraction) on two columns in the result set and  
average that value. I also need access to mysql's +minute+ function  
and +unix_timestamp+ function. I looked through the code but got lost  
pretty quickly when trying to figure out how to monkey patch this in.

  sql = "select market, minute(from_unixtime(ack)) AS minute,
round(avg((ack -
  created_at) * 1000), 2) AS ack_average from orders  where 'ack' >
  (UNIX_TIMESTAMP(now()) - (15 * 60) and 'market' = 'ICE' group by
  minute(from_unixtime(ack));"

  dataset = DB[sql]
  dataset.each {|r| p r}

I've played with it a bit and right now, field quoting in the mysql
adapter is broken, so you can't use any functions or formulas in
#select, #group etc. Ultimately our goal should look like this:

  market = 'ICE'
  ack_stamp = 15.minutes.ago
  Order.query do
    select do
      :market,
      :minute[:from_unixtime[:ack]].AS :minute
      :round[:avg[(:ack - :created_at) * 1000], 2].AS :ack_average
    end
    where do
      :ack > ack_stamp
      :market == market
    end
    group_by :minute[:from_unixtime[:ack]]
  end

With the help of ParseTree I think it's possible to do that, and also
to fix field quoting. Can you please open an issue for this?

On Nov 6, 2007 4:21 PM, Sharon Rosner <cico...@gmail.com> wrote:

 http://www.xaop.com/articles/2007/10/07/metaprogramming

HTH
Mark

from   :c => Customer, :b => Bank, :a => Account
  select b.name
  where  ((c.firstname == first1) |
          (c.firstname == first2)), etc.

appeared simpler than having blocks and .AS, but no doubt there is a
limit to his approach I'm unaware of.

Regards
Mark

  where(:firstname => [first1, first2])

Notice how you can provide an array and Sequel takes care of
constructing the filter. Also gives you nicer-looking SQL:

  ... WHERE firstname IN ('john', 'george')

If you need to qualify the field name you can use the special symbol
notation:

  where(:c__firstname => [first1, first2])

Sequel also lets you use any comparison operator, not just ==:

  where {:price > 100}

You can also specify mathemtic expressions and compare against other
fields:

  where {:price < :avg[:price] - 100}

Have a look at the cheat sheet:

  http://code.google.com/p/ruby-sequel/wiki/CheatSheet

On Nov 6, 2007 8:40 PM, Sharon Rosner <cico...@gmail.com> wrote:

On Nov 5, 2007, at 11:21 PM, Sharon Rosner wrote:

I'll take a stab at providing some of this functionality but it means  
learning ParseTree and probably ruby2ruby which might be a bit beyond  
my ruby capabilities right now. Don't hold your breath waiting for my  
patch... ;-)

http://sqldsl.rubyforge.org/

A potential problem is the fixation of dynamically generated values
such as the "market" or "ack_stamp" variables in the example code.