Hello members.
My goal
I would like to execute the following sql with sequel.
select path, max(mtime) as mtime, type from (
select path, mtime, 'server' as 'type'
from hoge
union
select path, mtime, 'client' as 'type'
from fuga
) as tmp
group by path;
Questions
It it possible to execute the following sql with sequel?
1, select .. from () as tmp
It it possible to execute from result of sql?
2, select max(mtime) as mtime..
It it possible alias max(:mtime) column as mtime?
Hoge.select{[:path, max(:mtime)]}
3, select 'sever' as type
It it possible select string as type column
select 'client' as 'type'
Best regards.
SQL setup scripts
create table hoge(
path varchar(255),
mtime datetime
);
create table fuga(
path varchar(255),
mtime datetime
);
delete from hoge;
delete from fuga;
insert into hoge('path', 'mtime') values('/samepath', '2014-09-01 0:0:0');
insert into hoge('path', 'mtime') values('/only_hoge', '2014-09-01 0:0:0');
insert into hoge('path', 'mtime') values('/newerhoge', '2014-09-01 0:0:1');
insert into fuga('path', 'mtime') values('/samepath', '2014-09-01 0:0:0');
insert into fuga('path', 'mtime') values('/only_fuga', '2014-09-01 0:0:0');
insert into fuga('path', 'mtime') values('/newerhoge', '2014-09-01 0:0:0');
Ruby code
require 'sqlite3'
require 'sequel'
require 'pp'
require 'logger'
DataBase = Sequel.connect("sqlite:///tmp/test.db")
logger = Logger.new($stdout)
DataBase.logger = logger
logger.level = Logger::DEBUG
class Hoge < Sequel::Model(:hoge)
end
class Fuga < Sequel::Model(:fuga)
end
pp DataBase
union_q = Fuga.select{[:path, :mtime]}
Hoge.select{[:path, max(:mtime)]}.union(union_q).each do |x|
pp x
end
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
