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.
