Yes, I would say that is an acceptable solution to me. My current attempt, which I think might work, is to grab all the primary keys off the original list of objects, then use those on an IN clause where I load only the primary key (by deferring everything else) of the parent and the desired collection. Then I can go through this second list and link the collections back to my original list based on primary key. This might not work all the time, but in our case all the Parent objects only have single column primary keys, so I think it will be ok.
So the basic strategy is this: 1) Do initial query for list of Parents 2) get list of primary keys from initialParents 3) query a second list of parents, using the primary keys on IN clause, with only primary key and collection loaded (I think I used add_entity to achieve this) 4) set the collections attribute of every initialParent that has a match in the secondParents list. I am also curious about this 3rd party extension, in case it is more robust, what is it called? On Monday, November 3, 2014 3:26:50 PM UTC-5, Michael Bayer wrote: > > Well to load multiple collections at once you need to give a list of > parent instances, were you hoping it would use an IN to achieve that or > otherwise what SQL are you looking for? There is a 3rd party extension > which does some of this and I was hoping to someday implement the IN style > of loading...which also is a slight bit more feasible to do in a delayed > fashion though I don't know that the 3rd party extension does this. > > Sent from my iPhone > > On Nov 3, 2014, at 2:52 PM, Kevin S <kevin...@gmail.com <javascript:>> > wrote: > > Ok, I think I understand some of the reasoning behind not wanting to > implement such a feature. I think this is fine, because I don't need the > magic that hibernate provides. > > However, I'm still stuck with the current problem of how to do this kind > of collection populating with our antique sybase instance. The kind of sub > queries needed to do what subqueryload provides, even if I am able to > modify the dialect, are probably not possible in our database version. > However, I know the lazy loading works for a single entity. Is there > anything I could easily modify to have subqueryload (or some extension of > it) trigger a second query, instead of wrapping the original search with a > left outer join? I imagine if this were implemented behind the scenes as > two separate requests, then you wouldn't have the same kind of ambiguous > cases you described above. > > Anyway, I'm not asking that you implement something into SQLAlchemy, more > looking for guidance on a way to support this kind of query (possibly via > some manual extension of the API). Sure, I could just write a specific > query to do this, but I want an approach that utilizes the details of each > collection's relationship, since this paradigm will be common in our app. > Even if it's a bit ugly to do, I think we'd be fine with it, since we only > need to support sybase for another year. Then we can migrate our app to > Postgres. > > > On Saturday, November 1, 2014 1:33:26 PM UTC-4, Michael Bayer wrote: >> >> >> > On Nov 1, 2014, at 11:53 AM, Kevin S <kevin...@gmail.com> wrote: >> > >> > I think I've seen this sort of functionality in Hibernate, where it >> tries to optimize if you are going to access some lazy loaded property on a >> collection of objects. It can load them in batches, as opposed to one query >> for every object. >> >> >> we offer the “subqueryload” feature. However this load is not >> “on-demand” when an attribute is accessed, it’s established up front as the >> total collection of objects are loaded. >> >> Hibernate’s feature of doing this on demand is not something I can get my >> head around, really. It links the loading of a collection to how the >> object was loaded in the first place, and to the query that loaded it. I >> can hardly count the number of ambiguous edge cases that come to mind with >> that. What if query A loads objects X, Y, Z, query B loads objects P, Q, >> Y, then I access P.collection, does Y.collection get loaded, and if so, >> what if I had accessed X.collection instead? If we say that query A >> should stay with object Y, what about the subtle case where Y falls out of >> scope in the user’s application, then is loaded with query B, now we have >> Y.collection that may be loaded from A or may be loaded from B based on a >> subtlety of memory management which will confuse users. It’s not just >> that a query of X.collection or P.collection can trigger Y.collection - >> queries A and B might have different options set up which can change how >> related objects past Y.collection are loaded too. Overall, how do I >> manage memory, if 1000 objects were loaded with 1000 queries I have to >> carry around all 1000 query objects with each object? What if the object >> is serialized? What if the object was expired, does that expire that it’s >> connection to its original query? >> >> I would imagine that in Hibernate’s case, these edge cases just exist in >> some awkward way and there’s not much way to question it - this was my >> observation with a lot of Hibernate behaviors :) - everything sort of >> worked, though once in awhile not, and if you could even find a community >> where people were describing and questioning the behavior clearly they >> weren’t really getting any answers. This is a big reason Hibernate, >> while being brilliant software that I’ve learned much from and used >> extensively, has still managed to earn kind of a bad reputation regardless. >> For SQLAlchemy, this feature it seems a little more exotic than what >> folks need and would come at a significant cost in terms of maintenance, >> dealing with inconsistencies in edge cases and end-user confusion, which we >> take very seriously. >> >> So anyway yes I’m familiar with this feature and I implemented >> “subqueryload” as a response to it, e.g. same kind of loading idea but >> avoids the ambiguity of linking every object to a set of originating >> queries. this is not to say this feature couldn’t be implemented but it >> seems like a steep hill to climb. > > -- > 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 sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
