OK, so I translated this code into what I'm really doing, and it worked great. Then I systematically stripped out all the parts that differ from what I had before -- using core extensions, the dataset mutation methods, using Sequel.function(:max) instead of the virtual row -- .and it still all works.
I'm going to spend tomorrow trying to figure out what I was doing wrong, but as for now I'm just plain flummoxed, and incredibly grateful for the speedy and unjustifiably patient response. Thanks so much! On Tuesday, April 29, 2014 4:51:10 PM UTC-4, Bill Dueber wrote: > > *The goal: Given a one-to-many relationship between tables A and B, get > output from a single query where each row of output has a bunch of stuff > from A and a bunch of stuff from exactly one of the > possibly-many-matched-rows in B.* > > I’ve read, and played, and played, and read, and while I didn’t think I > was particularly stupid before starting this, I’m beginning to wonder… > > I also know this is supposedly well-trodden ground, but ...well, see above > about being stupid. I can't find anything clear enough to get past the hump. > > Let’s say for the sake of argument that I’ve got a table of users and an > ordered table of their classes: > > create table usr (id int, name varchar(255));insert into usr values (1, > 'bill');insert into usr values (2, 'mike'); > create table classes (user_id int, seq int, class varchar(255));insert into > classes values (1, 1, 'math');insert into classes values (1, 2, > 'reading');insert into classes values (2, 1, 'english');insert into classes > values (2, 2, 'gym'); > > What I want is a listing of users and their last class of the day (as > denoted by the highest seq value). > > 1 Bill reading > 2 Mike gym > > The SQL I have for this isn’t exactly straightforward; there may be better > ways, but I don’t want to do multiple queries. Here’s one way. > > select u_top.*, c_top.class from usr u_top, classes c_topwhere c_top.seq = > (select max(seq) from classes where classes.user_id = u_top.id and u_top.id = > c_top.user_id); > > When I try to reproduce that in the obvious way using sequel, it all fails. > > ds = DB[:usr.as(:u_top), :classes.as(:c_top)] > ds.select_all!(:u_top) > ds.select_append!(:c_top__class) > > inner = DB[:miu01__classes.as(:c_inner)] > inner.select!(Sequel.function(:max, :c_inner__seq)) > inner.where!(:c_inner__user_id => :c_top__user_id, :c_top__user_id => > :u_top__id) > > ds.where!(:c_top__seq => inner) > > ds.all #=> [] > > But the kicker is that when I cut-and-paste the result of ds.sql into a > command shell, I get the expected output. > > I’m sure I’m not properly aliasing the tables in some way and the > underlying logic engine probably needs me to do an explicit join or graph > or something, but I can’t figure it out to save my soul. > > Thanks for any advice… > > -Bill- > -- 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.
