Quick update: with those changes, doing the following...
vendors = Vendor
.where(:user_id => 1)
.eager(:last_edit)
.all
yields a query of:
INFO -- : (0.000693s) SELECT * FROM (SELECT * FROM `vendor_revisions` WHERE
(1 = `vendor_revisions`.`vendor_id`) ORDER BY `created_at` DESC LIMIT 1) AS
`t1` UNION ALL SELECT * FROM (SELECT * FROM `vendor_revisions` WHERE (2 =
`vendor_revisions`.`vendor_id`) ORDER BY `created_at` DESC LIMIT 1) AS `t1`
and it returns the correct values. Is that the expected behavior?
Thanks again,
Greg
On Thursday, December 29, 2016 at 8:43:54 PM UTC-5, Greg Gilbert wrote:
>
> Sorry about the Docker setup, thought it might be easier. I'll remember
> for next time.
>
> I see what you're saying about adding the :order. Doing that on the
> association is cleaner than the proc.
>
> I turned on debug to look at the queries, and it looks like doing it that
> way creates N+1 queries, i.e.:
>
> INFO -- : (0.000408s) SELECT * FROM `vendors` WHERE (`user_id` = 1)
> INFO -- : (0.000509s) SELECT * FROM `vendor_revisions` WHERE
> (`vendor_revisions`.`vendor_id` = 1) ORDER BY `created_at` DESC LIMIT 1
> INFO -- : (0.000356s) SELECT * FROM `vendor_revisions` WHERE
> (`vendor_revisions`.`vendor_id` = 2) ORDER BY `created_at` DESC LIMIT 1
>
> Isn't that less efficient than doing it all in one query and sorting it
> out?
>
> Thanks,
> greg
>
> On Thursday, December 29, 2016 at 2:14:00 AM UTC-5, Jeremy Evans wrote:
>>
>> On Wednesday, December 28, 2016 at 10:03:55 PM UTC-8, Greg Gilbert wrote:
>>>
>>> Hey,
>>>
>>> I put together a proof of concept using Docker:
>>> https://github.com/greggilbert/sequel-association-test
>>>
>>> If you look at doit.rb, you'll see two queries. The first one doesn't
>>> work (i.e. returns nil) but the second one does. I guess there's
>>> something about the eager_limit that's not right? And if you leave out
>>> the .eager line altogether, you get the first VendorRevision for each,
>>> not the last.
>>>
>>> Let me know if there's anything else I can clarify.
>>>
>>
>> In the future, please do not submit examples that use docker, or split
>> code between multiple files. Please combine all code in a single post to
>> this list. This saves me time as I wouldn't have to go through 4 separate
>> files to piece together a working example just to provide support. Below
>> is a self contained example I made from your repo that is runnable with
>> bin/sequel (e.g. ruby bin/sequel -E sqlite:/ filename.rb).
>>
>> In any case, :eager_limit should not be used for one_to_one associations,
>> and it doesn't make sense to use it as the limit should always be 1 anyway
>> (it can make sense for *_to_many associations). Using :eager_limit for
>> one_to_one associations is undefined behavior, so the fact that it breaks
>> something is not a bug.
>>
>> You'll notice in the code below that I commented out the :order option to
>> the last_edit association. From looking at your example, I would guess
>> that you would want to specify that option, and then you'll be able to use
>> the last block in the example, which doesn't use an eager callback. This
>> is best as it produces the most optimized query. It seems to me unlikely
>> that you'd ever want to use the last_edit association without a reverse
>> chronological order.
>>
>> Thanks,
>> Jeremy
>>
>> DB.instance_exec do
>> create_table(:users) do
>> primary_key :id
>> String :email, null: false
>> String :password_hash, null: false
>> DateTime :created_at, null: false, default:
>> Sequel::CURRENT_TIMESTAMP
>> DateTime :updated_at, null: false, default:
>> Sequel::CURRENT_TIMESTAMP
>> end
>>
>> create_table(:vendors) do
>> primary_key :id
>> foreign_key :user_id, :users
>> Boolean :is_verified, default: false
>> end
>>
>> create_table(:vendor_revisions) do
>> primary_key :id
>> foreign_key :vendor_id, :vendors, null: false, index: true
>> String :name, null: false, index: true
>> DateTime :created_at, default: Sequel::CURRENT_TIMESTAMP
>> DateTime :updated_at, default: Sequel::CURRENT_TIMESTAMP
>> end
>>
>> alter_table(:vendors) do
>> add_foreign_key :latest_revision, :vendor_revisions, null: true
>> end
>> end
>>
>> class User < Sequel::Model
>> one_to_many :vendors
>> end
>>
>> class VendorRevision < Sequel::Model
>> many_to_one :vendor
>> end
>>
>> VendorRevision.plugin :timestamps, update_on_create: true
>>
>> class Vendor < Sequel::Model
>> one_to_many :vendor_revisions, class: VendorRevision
>> many_to_one :user
>> end
>>
>> Vendor.one_to_one :last_edit, :class => :VendorRevision#,
>> :order=>Sequel.desc(:created_at)
>>
>> u1 = User.create(:email => '[email protected]', password_hash: 'foo')
>> u2 = User.create(:email => '[email protected]', password_hash: 'foo')
>> v1 = Vendor.create(:user_id => u1.id)
>> v2 = Vendor.create(:user_id => u1.id)
>> v3 = Vendor.create(:user_id => u2.id)
>> vr1 = VendorRevision.create(:vendor_id => v1.id, :name => 'Test one')
>> vr3 = VendorRevision.create(:vendor_id => v2.id, :name => 'Test three')
>> vr2 = VendorRevision.create(:vendor_id => v1.id, :name => 'Test two')
>> vr4 = VendorRevision.create(:vendor_id => v2.id, :name => 'Test four')
>>
>> vendors = Vendor
>> .where(:user_id => 1)
>> .eager(:last_edit => proc {|ds|
>> ds.reverse_order(:created_at).clone(:eager_limit => 1) })
>> .all
>>
>> vendors.each do |v|
>> p v.last_edit
>> p '-'*50
>> end
>>
>> vendors = Vendor
>> .where(:user_id => 1)
>> .eager(:last_edit => proc {|ds|
>> ds.reverse_order(:created_at) })
>> .all
>>
>> vendors.each do |v|
>> p v.last_edit
>> p '-'*50
>> end
>>
>> vendors = Vendor
>> .where(:user_id => 1)
>> .eager(:last_edit)
>> .all
>>
>> vendors.each do |v|
>> p v.last_edit
>> p '-'*50
>> 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 https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
