On Feb 8, 2008, at 3:49 PM, Richard Levasseur wrote:
>
> Hm, we could do that, but that means that we have to outerjoin to
> ~15 other tables. The primary table has ~200+ columns on it, each
> child table has 10-20 columns, there are over a million records, and
> our base filter criteria can be a bit complex. Its indexed and
> such, but the query needs to run in < 0.7 seconds (about how fast it
> goes on dev). I have concerns it won't perform as well (we tried
> that and it really killed performance, but that was prior to a lot
> of performance improvements we've made, so it might be feasible
> again).
you don't have to join to 15 tables. You can also set select_table to
the base table only, then write your mapper extension to do the work
of _post_instance(). This extension calls the main populate_instance
first, then issues a second query for the child table which is
conditional. This example is "hardcoded" to the example "kids" table
but can be generalized if needed. Notice that it populates the "kid"
attributes with some default values, which is needed here because
otherwise hitting them later will trigger a "broken" load (because
there's no row in "kids"):
class PostLoaderExtension(MapperExtension):
def populate_instance(self, mapper, selectcontext, row, instance,
**flags):
mapper.populate_instance(selectcontext, instance, row, **flags)
statement = sql.select([kids], kids.c.pid==instance.id,
use_labels=True)
row =
selectcontext.session.connection(mapper).execute(statement).fetchone()
if row:
identitykey = mapper.identity_key_from_instance(instance)
mapper.populate_instance(selectcontext, instance, row,
isnew=False, instancekey=identitykey, ispostselect=True)
else:
instance.pid = instance.id
instance.cid = None
return EXT_STOP
mapper(Parent, parents, polymorphic_on=parents.c.type,
polymorphic_identity="parent", select_table=parents,
extension=PostLoaderExtension())
mapper(Child, kids, inherits=Parent, polymorphic_identity="child")
# Insert a Child, but the record in the child table is missing
engine.execute("insert into parents (id, type) values (1, 'child')")
engine.execute("insert into parents (id, type) values (2, 'child')")
engine.execute("insert into kids (pid, cid) values (2, 5)")
session = Session()
print "*" * 70
for p in session.query(Parent):
print p, p.cid
its the "guessing" above as to what should be populated into "pid" and
"cid" that makes this a rough feature to add to SA core but not such a
big deal as an extension.
>
> Thinking about this more, I essentially want to eagerload certain
> inherited child tables on a case-by-case basis. I think I've seen
> similar questions about that here already. I don't know the
> internals, but it seems like a parent knows about its children
> through its polymorphic map, so can't it figure out all the tables
> it would have to join to on its own?
if by "eagerload" you mean, "issue a second query for", then the above
approach will get you started. if you mean that it should construct
joins from parent to child table, well yes it already does that if you
query for a Child specifically, i.e. query(Child).all(). you also
said you dont want to use a join for the base mapper since theres too
many tables, so i dont think that's where you're referring to. You
can put select_table on whichever mappers you want to control who
loads from what kinds of tables. If you want to send through certain
selectables on a per-query basis that is doable as well. I can't give
more advice without specifically seeing what you mean by a "case-by-
case" basis.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
