Thanks for all the info Mike. I sort of figured that was the case as I started writing out my question — apologies for bothering you with it anyway!
Awesome release, by the way. Loving all the new features. Thanks, as ever, for all your hard work. On Thursday, 17 August 2017 00:09:40 UTC+1, Mike Bayer wrote: > > On Wed, Aug 16, 2017 at 7:48 AM, Aidan Kane <aida...@gmail.com > <javascript:>> wrote: > > I'm sure there's a great reason for this, but why does the new selectin > > loader join the parent table and use the parent ids instead of using the > > foreign key directly on the child table? Is it just the complexity of > > knowing what to filter for / how to build the criteria for the selectin > > query? > > that's because a relationship() can have a primaryjoin / secondaryjoin > that is anything. It is not a given that the primary key of the > parent object is present in the criteria we use to get the child > objects. > > > > > In my case I have a parent table with 55,000 rows selected. There are > then > > 323 distinct child records. By joining the parent table it means I end > up > > with stackloads of queries where 1 would do. > > this has nothing to do with whether the JOIN is there, the definition > of selectin loading is that it uses "IN" against a list of parent > primary key identifiers, in batches of 500 each (which will be > configurable at some point, but you don't want to go much over 1000 > for that size in any case) so it is by design for 55K parents you'd > expect 110 queries. > > If you want to emit *one* query that loads all 323 child records at > once regardless of parent records, use the subquery loader instead. > Still uses a JOIN but joins to the original query that returns your > 55K rows. > > > > > > > > Is it be possible to have it filter for all the distinct child ids > first, > > and then use those for the IN filter directly on the child table without > > joining the parent? > > > > I guess the normal usage is for it to work in the other direction where > the > > parent is the smaller table and the child has many records? > > > > -- > > SQLAlchemy - > > The Python SQL Toolkit and Object Relational Mapper > > > > http://www.sqlalchemy.org/ > > > > To post example code, please provide an MCVE: Minimal, Complete, and > > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > > description. > > --- > > 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 https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
