Hello. Just a minor correction:
The new query can return whatever it pleases as long as it *also* returns Foo instances with properly populated relations. The rest of the e-mail is the same. Please help me, Ladislav Lenart On 13.6.2013 17:03, Ladislav Lenart wrote: > Unfortunately migrating to SA 0.9 is not an option for me at the moment due to > severe time constraints. > > 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. > > Here is a simplified version of my problem: > > Foo > bar (can be NULL) > > Bar > > BarA (subclass of Bar) > data -> Data (cannot be NULL) > > Data > value (string) > > I need to rewrite this query: > > q = session.query(Foo).filter(Foo.id.in_(...)) > q = q.options( > joinedload_all(Foo.bar, BarA.data) > ) > > in such a way that it does NOT perform any full scan and also populates > Foo.bar.data.value of each returned Foo. The new query can return whatever it > pleases as long as it returns Foo instances with properly populated relations. > > Please help me write it (or tell me that it is not possible in SA 0.7.9, > though > I highly doubt that). > > > Thank you, > > Ladislav Lenart > > > On 13.6.2013 15:51, Michael Bayer wrote: >> Please try out 0.9 from the git master which fixes the issue of the nested >> SELECT on the right side of a join. >> >> Sent from my iPhone >> >> On Jun 13, 2013, at 9:18 AM, Ladislav Lenart <lenart...@volny.cz> wrote: >> >>> Hello. >>> >>> I have a query that does a full scan of an inherited table with more than >>> million rows even though I need only 100 of them (on postgres 9.1). This is >>> a >>> real bummer! Please help me rewrite the SA query or instruct postgres to >>> not do >>> this stupidity. >>> >>> >>> I have the following setup (only the interesting relations): >>> >>> Contact >>> contact_tags -> ContactTag (collection) >>> phones -> Phone (collection) >>> emails -> Email (collection) >>> >>> ContactTag >>> tag -> Tag (cannot be NULL) >>> >>> PersonalContact (Contact subclass) >>> partner -> Partner (can be NULL) >>> client -> PersonalClient (can be NULL) >>> >>> CorporateContact (Contact subclass) >>> client -> CorporateClient (can be NULL) >>> >>> Client >>> >>> PersonalClient (Client subclass) >>> data -> PersonalData (cannot be NULL) >>> >>> CorporateClient (Client subclass) >>> data -> CorporateData (cannot be NULL) >>> >>> >>> I have the following query that loads data of one window: >>> >>> # window (input argument) is a list of id values. >>> q = session.query(PersonalContact).filter(PersonalContact.id.in_(window) >>> q = q.options( >>> subqueryload_all(PersonalContact.contact_tags, ContactTag.tag), >>> subqueryload(PersonalContact.phones), >>> subqueryload(PersonalContact.emails), >>> joinedload_all( >>> PersonalContact.partner, >>> Partner.personal_data, >>> PersonalData.address, >>> ), >>> joinedload_all( >>> PersonalContact.client, >>> PersonalClient.data, >>> PersonalData.address >>> ), >>> ) >>> >>> >>> (Note that I have similar query for CorporateContact. Infact, I generate >>> them >>> both in the same method.) >>> >>> It produces SQL like this (the problematic part is emphasized): >>> >>> SELECT * >>> FROM >>> contact >>> JOIN personal_contact ON contact.id = personal_contact.id >>> -- *************** >>> LEFT OUTER JOIN ( >>> SELECT * >>> FROM >>> client >>> JOIN personal_client ON client.id = personal_client.id >>> ) AS anon_1 ON personal_contact.client_id = anon_1.personal_client_id >>> -- *************** >>> LEFT OUTER JOIN partner AS partner_1 >>> ON personal_contact.partner_id = partner_1.id >>> LEFT OUTER JOIN personal_data AS personal_data_1 >>> ON partner_1.personal_data_id = personal_data_1.id >>> LEFT OUTER JOIN address AS address_1 >>> ON personal_data_1.address_id = address_1.id >>> LEFT OUTER JOIN personal_data AS personal_data_2 >>> ON anon_1.personal_client_data_id = personal_data_2.id >>> LEFT OUTER JOIN address AS address_2 >>> ON personal_data_2.address_id = address_2.id >>> WHERE personal_contact.id IN (...) >>> >>> >>> The inner select directly corresponds to joinedload of >>> PersonalContact.client, a >>> PersonalClient instance (and a Client subclass). >>> >>> The postgres does a full scan of tables Client and PersonalClient even >>> though I >>> will need at most 100 rows from each one. >>> >>> However, if I rewrite the problematic part by hand like this: >>> >>> LEFT OUTER JOIN client >>> ON personal_contact.client_id = client.id >>> LEFT OUTER JOIN personal_client >>> ON client.id = personal_client.id >>> >>> it works like a charm. >>> >>> Unfortunately I don't know how to write such a query in SA. I am really >>> stuck so >>> any help is much appreciated. >>> >>> >>> Thank you, >>> >>> Ladislav Lenart >>> >>> >>> -- >>> 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?hl=en. >>> For more options, visit https://groups.google.com/groups/opt_out. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.