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 <[email protected]> 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 [email protected].
>>> To post to this group, send email to [email protected].
>>> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
