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)
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.
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.
I hope I'm missing something obvious ;)
R
--
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/-/iTPhp7hy0WcJ.
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.
