On Monday, December 10, 2012 7:50:02 PM UTC-8, Russell Fulton wrote:
> Hi
> First a little background:
>
> I am converting a lot of old perl code to ruby -- many of these make
> moderately complex sql queries to databases. The perl scripts used DBI and
> I could just translate stuff to ruby DBI but for various reasons I decided
> to try and use Sequel instead,
>
> So far I have spent a couple of frustrating days trying to figure out how
> to build complex where clauses which have nested ands and ors in them.
> They also call sql function like sub_date.
>
> Here is a query that attempts to find a particular session given an IP and
> a time from a table of sessions...
>
> puts = DB[:sessions].select(:upi, :start_time, :stop_time).
> where{ Sequel.&( :ip_address == ip.to_i ,
> Sequel.|( :stop_time == nil &
> Sequel.function(:date_sub, :start_time, 'interval 5 minute') < time ,
> Sequel.&( time >
> Sequel.function(:date_add, :start_time, 'interval 5 minute') ,
> time < :stop_time ))
> )
> }.
> order(:start_time).sql
>
> whether I a even remotely on the right track I really don't know -- I
> could not find any examples of complex where clauses.
>
> it dies complaining :
> est-sequel.rb:26: undefined method `function' for Sequel:Module
> (NoMethodError)
>
What version of Sequel are you running? My guess is an older version,
before Sequel.function was added. Try installing the latest version.
As for whether you are on the right track, try building your query
incrementally in an irb shell:
$ sequel
irb(main):001:0> DB[:sessions]
=> #<Sequel::Mock::Dataset: "SELECT * FROM sessions">
irb(main):002:0> DB[:sessions].select(:upi, :start_time, :stop_time)
=> #<Sequel::Mock::Dataset: "SELECT upi, start_time, stop_time FROM
sessions">
irb(main):003:0> DB[:sessions].select(:upi, :start_time,
:stop_time).order(:start_time)
=> #<Sequel::Mock::Dataset: "SELECT upi, start_time, stop_time FROM
sessions ORDER BY start_time">
irb(main):004:0> DB[:sessions].select(:upi, :start_time,
:stop_time).order(:start_time).where{inet_aton(1)}
=> #<Sequel::Mock::Dataset: "SELECT upi, start_time, stop_time FROM
sessions WHERE inet_aton(1) ORDER BY start_time">
This makes it much easier to play around and see how things work.
If you want to read some documentation, you may want to start with Sequel
for SQL Users: http://sequel.rubyforge.org/rdoc/files/doc/sql_rdoc.html
>
> the original sql looked like this:
>
> my $sql = qq|SELECT upi, start_time, stop_time FROM sessions WHERE
> INET_ATON('$args{ip}') = ip_address AND (( stop_time is NULL and
> date_sub(start_time, interval 5 minute) < '$args{time}' ) or (
> '$args{time}' BETWEEN date_sub(start_time, interval 5 minute) AND STOP_TIME
> ) ) order by start_time desc limit 1|; #
>
> On reflection there is probably no point in abstracting this query as
> date_sub is mysql specific but some pointers on how to build complex where
> clauses and use builtin functions would be welcome.
>
Well, you can certainly just use plain SQL if you are only planning
on targeting a single database. What you have seems to be fairly good,
except for the use of ==. Equality is expressed with hashes in Sequel, not
with == (e.g. "{:ip_address => ip.to_i}", not ":ip_address == ip.to_i ").
Currently, Sequel offers a database independent extract function, but no
database independent date arithmetic functions.
> I did find the filter document but could not make much of the stuff there
> work :( and it is far from clear how to do arbitrary boolean combination
> in any manner which does not end up with something that is much more opaque
> than the raw sql.
>
For complex static cases targeting a single database, literal SQL is often
going to look and work better. The advantage of Sequel's DSL is it is
database independent (in some cases) and it makes it easier to handle
dynamic cases.
Thanks,
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/-/YNxil_RfLMIJ.
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.
