On Apr 20, 2014, at 8:19 AM, Christopher Monsanto <[email protected]> wrote:
> Hi all, > > I have two classes, A and B, and an m2m relationship a_bs. A.bs is mapped to > B with secondary=a_bs and vice versa. > > My problem is that I need to eagerly populate A.bs and B.as using a single > SQL query. lets consider what that really means. suppose there is an A, id #1 in the result. A.bs for #1 suppose has a B in it with id #2. B.as for id #2 suppose has an A with id #3 in the result. So really, loading in this way can end up loading the entire database, even if you only started with a handful of As or Bs. I have no idea how you want to handle that. So the challenge here is exactly how to figure out what this SQL would be. You have your list of As and your list of Bs, I guess you'd need to select A join to atob join to B, where you select for rows in atob that are in either of your original A or B lists, either using IN with a list of ids or against a subquery. In any case, from a SQLA perspective, SQLA isn't coming up with that SQL for you. If you're looking to automate it, figure out what the inputs and outputs of the query would be, and then a function can be produced to render it. Then to populate, execute the query, as it loads As and Bs that are known to be part of certain collections, populate lists keyed to those identifiers. Then apply those lists directly to the target A and B objects using set_committed_value(). An example of this is here: https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/DisjointEagerLoading > > Some notes and constraints: > > - I imagine there are some tricks one can do using back population, such as > eagerly loading all As and A.bs, and just expecting B.as to be populated. > That might be appropriate if I was writing these queries manually and could > document such behavior, but all of this happens in a metaprogramming context > and it's easiest to maintain my invariants if the loading is symmetric and > explicit. > > - I could noload(A.bs) and noload(B.as), and select(a_bs) and manually wire > everything up. The problem here is that noloaded attributes still track > writes. For this approach to work, I need to be able to say to the session > "trust me, this object is already in the collection, don't mess with it". > Ideally after this process the internal state of the list would be the same > as if I eagerloaded it normally. > > - I cannot add an explicit AB association class to my model. Speed and memory > usage is very important here, and spending time creating lots of unused > objects won't help. > > What's the best way to go about this? > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" 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/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" 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/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
