Jeremy, thanks for the considered response.
On Thursday, 29 May 2014 01:07:18 UTC+9:30, Jeremy Evans wrote:
>
> On Wednesday, May 28, 2014 7:46:17 AM UTC-7, Andrew Hacking wrote:
>>
>> Hi,
>>
>> I am finding that when using the association_pks plugin that I am getting
>> 1+N queries on many_to_many associations.
>>
>> When using eager loading (either explicitly or with the tactical eager
>> loading plugin) the eager loaded dataset is not used.
>>
>> After reading the code of the plugin I noticed that the join table is
>> used and that select_map() is called on it to get the foreign keys. This
>> results in a separate query for each record in the original dataset.
>>
>> Ideally I don't want association_pks to eager join with the target table
>> in the many_to_many case since all I want are the foreign keys from the
>> join table, but I don't want 1+N queries either.
>>
>> The ideal approach would be to use the eager loaded or tactically eager
>> loaded dataset for the many_to_many association if present, or tactically
>> eager load just the join table (ie not the target table) and be able to use
>> that dataset with each item in the original/parent dataset.
>>
>> This requires a fair amount of knowledge on the internals of how sequels
>> eager loading works so any tips on the feasibility for doing something like
>> this would be appreciated. This is a very common use case in my json based
>> web api where associated keys are both returned and modified so I'd like to
>> make it work efficiently.
>>
>> I know I can just call map on the eager loaded association directly to
>> get the ids but its both clumsy and the generated SQL does an extra join
>> against the target table and returns more columns than I need. The
>> association_pks plugin ought be able to do it more efficiently by just
>> eagerly pulling all the foreign keys from the join table in one go.
>>
>> I'm also using Sequel 4.10, but it was behaving the same on earlier
>> versions as well.
>>
>
> As select_map is a dataset method, it cannot work with eager loading. To
> have it work with eager loading, you'd have to change the *_pks getter to
> look at the associated objects and call methods on them. However, that
> apparently isn't exactly what you want, as it does an extra join and
> returns more columns than you need. The more columns you can deal with
> fairly easily by adding a eager callback: eager(:assoc=>proc{|ds|
> ds.select(...)}), but if you want to avoid the extra join, there is nothing
> in Sequel that will do what you want currently. I'm not OK with changing
> the default behavior, but I might consider adding an option to the plugin
> so that it calls methods on the associated objects instead of using
> select_map.
>
> For the most optimized approach, I would add a class method that takes an
> array of objects and gets the associated pks for all of them in a single
> query (select_hash_groups should work for that) and stores them in @*_pks
> in each object, and then override the *_pks instance method to look for
> @*_pks and return it if present (and call super if not). I'm not sure if
> something like that can be made generic easily, but if you come up with
> something that you think will work in a generic way, please let me know.
>
> 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.
