On Tuesday, April 29, 2014 1:51:10 PM UTC-7, Bill Dueber wrote:
>
>
> 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.
>
The following returns correct results for me in SQLite:
DB.create_table(:usr){Integer :id; String :name}
DB.create_table(:classes){Integer :user_id; Integer :seq; String :class}
DB[:usr].import([:id, :name], [[1, 'bill'], [2, 'mike']])
DB[:classes].import([:user_id, :seq, :class], [[1, 1, 'math'], [1, 2,
'reading'], [2, 1, 'english'], [2, 2, 'gym']])
ds = DB[:usr___u_top, :classes___c_top].
select_all(:u_top).
select_append(:c_top__class).
where(:c_top__seq=>DB[:classes___c_inner].
select{max(:c_inner__seq)}.
where(:c_inner__user_id => :c_top__user_id, :c_top__user_id =>
:u_top__id))
ds.all
This is mostly the same as your example except that it avoids the use of
core extensions, uses triple underscore symbols for aliasing, doesn't use
the dataset mutation methods, and uses a virtual row for the max function
in the subquery. Also, your example used :miu01__classes, which I assume
should just be :classes in this example.
Can you try that on your database (Oracle?) and see what it returns?
Thanks,
Jeremy
--
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.
