Hello. I ended up with the following query:
@classmethod def _find_contacts_fetch_window(cls, contact_cls, win): """Special data-fetching query for contacts and all their related info including tags, partner, client,... NOTE: We build the FROM part entirely by hand, because SA generates bad SQL for postgres. It does a FULL SCAN of client and personal_client / corporate_client even though it reads at most window_size rows from them. All this because SA inheritance creates a subselect which leads to the full scan. """ # win .. list of ids # contact_cls .. PersonalContact / CorporateContact client_cls = contact_cls.client_cls() # PersonalClient / CorporateClient data_cls = client_cls.data_cls() # PersonalData / CorporateData # We need TABLEs to build the FROM part by hand. # We reference PersonalData/CorporateData and Address twice, hence we # need to alias them. # We also need their aliased ORM classes for contains_eager() to work. contact_table = Contact.__table__ contact_subtable = contact_cls.__table__ client_table = Client.__table__ personal_client_table = client_cls.__table__ partner_table = Partner.__table__ partner_data_table = PersonalData.__table__.alias(name='partner_data') partner_address_table = Address.__table__.alias(name='partner_address') client_data_table = data_cls.__table__.alias(name='client_data') client_address_table = Address.__table__.alias(name='client_address') partner_data = aliased(PersonalData, partner_data_table) partner_address = aliased(Address, partner_address_table) client_data = aliased(data_cls, client_data_table) client_address = aliased(Address, client_address_table) select_from = contact_table.join( contact_subtable, contact_table.c.id == contact_subtable.c.id ).outerjoin( client_table, contact_subtable.c.client_id == client_table.c.id ).outerjoin( personal_client_table, client_table.c.id == personal_client_table.c.id ).outerjoin( client_data_table, personal_client_table.c.data_id == client_data_table.c.id ).outerjoin( client_address_table, client_data_table.c.address_id == client_address_table.c.id ) options = [ subqueryload_all(contact_cls.contact_tags, ContactTag.tag), subqueryload(contact_cls.phones), subqueryload(contact_cls.emails), contains_eager(contact_cls.client), contains_eager(contact_cls.client, client_cls.data, alias=client_data), contains_eager(contact_cls.client, client_cls.data, client_data.address, alias=client_address), ] if contact_cls is PersonalContact: select_from = select_from.outerjoin( partner_table, contact_subtable.c.partner_id == partner_table.c.id ).outerjoin( partner_data_table, partner_table.c.personal_data_id == partner_data_table.c.id ).outerjoin( partner_address_table, partner_data_table.c.address_id == partner_address_table.c.id ) options.extend([ contains_eager(contact_cls.partner), contains_eager(contact_cls.partner, Partner.personal_data, alias=partner_data), contains_eager(contact_cls.partner, Partner.personal_data, partner_data.address, alias=partner_address), ]) q = session.query(contact_cls).select_from(select_from) q = q.filter(contact_cls.id.in_(win)) q = q.options(*options) return q It works great though it took me quite a while to get right, especially the aliasing between tables and classes. Also, I wouldn't figure it out without your help (I mean the part with select_from). Thank you again, Ladislav Lenart On 13.6.2013 18:44, Michael Bayer wrote: > > On Jun 13, 2013, at 11:03 AM, Ladislav Lenart <lenart...@volny.cz> wrote: > >> Unfortunately migrating to SA 0.9 is not an option for me at the moment due >> to >> severe time constraints. > > I was pretty sure you'd say that, though I'm really looking to verify that my > fixes are going to hold up under real world usage. The issues you're having > are real issues, and they've been fixed. > >> >> Could you please help me write SA query for 0.7.9 that uses index scan and >> also >> loads all the necessary relations? It must be possible with a proper use of >> from_statement(), contains_eager() and/or other SA features. It is just that >> toy >> examples in the documentation don't help me much with this complex beast. > > you use the SQL expression language in conjunction with .join()/outerjoin(), > pass to query.select_from(), then use contains_eager(): > > j = > Foo.__table__.outerjoin(Bar.__table__.outerjoin(BarA.__table__).outerjoin(Data.__table__)) > > q = s.query(Foo).\ > select_from(j).\ > filter(Foo.id.in_([1, 2, 3])).\ > options( > contains_eager(Foo.bar), > contains_eager(Foo.bar.of_type(BarA), BarA.data) > ) > > > -- 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/groups/opt_out.