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