Hello.
On 25.9.2013 17:15, Michael Bayer wrote:
>
> On Sep 25, 2013, at 10:11 AM, Ladislav Lenart <lenart...@volny.cz> wrote:
>
>> Hello.
>>
>> Would it be possible to make these two forms
>>
>> session.query(cls).options(
>> subqueryload(cls.foos),
>> subqueryload(cls.foos, Foo.bar),
>> )
>>
>> and
>>
>> session.query(cls).options(
>> subqueryload_all(cls.foos, Foo.bar)
>> )
>>
>> completely equivalent, i.e. subqueryload_all being just a shortcut of the
>> former
>> if the paths have the above pattern (i.e. if one subqueryload is subsumed by
>> another)?
>
> unless there's some bug I'm unaware of, those two forms are exactly
> equivalent. XYZload_all("a.b.c") is a shortcut to XYZload("a"),
> XYZLoad("a.b"), XYZLoad("a.b.c"). In 0.9 I'm working out a new system
> backing the loader options that will allow more intuitive patterns, e.g.
> load(cls).subqueryload("foos").subqueryload("bar").
Hmm, that's not what I see here. I captured SQL Alchemy debug log (echo=True) of
one such query and modified it to be easier to read, added corresponding python
code and also my remarks/questions. Please look at it, though it's rather
long...
First, a query that returns ids of ALL Partner instances of the result (this is
how I do windowed queries).
[python]
session.query(distinct(Partner.id)).filter(
Partner.visible == True,
Partner.sponsor_id == self.id,
)
[sql]
SELECT DISTINCT partner.id AS anon_1
FROM partner
WHERE partner.visible = true AND partner.sponsor_id = %(sponsor_id_1)s
This result list of (partner) ids is then divided to windows of size 100. Each
window is queried individually. Following are the queries to completely populate
one such window.
Main query to populate data of max 100 partners (one window).
[python]
# win is a list of 100 ids.
session.query(Partner).filter(
Partner.id.in_(win)
).options(
joinedload(Partner.subject, innerjoin=True),
joinedload(Partner.subject, Subject.subject_personal, innerjoin=True),
joinedload(Partner.subject, Subject.subject_personal,
SubjectPersonal.address),
subqueryload(Partner.subject, Subject.contacts),
subqueryload(Partner.subject, Subject.contacts, Contact.contact_personal),
subqueryload(Partner.subject, Subject.contacts, Contact.contact_personal,
ContactPersonal.contact_address),
subqueryload(Partner.subject, Subject.contacts, Contact.contact_personal,
ContactPersonal.permanent_address),
subqueryload_all(Partner.subject, Subject.tag_subjects, TagSubject.tag),
subqueryload_all(Partner.partner_regions),
)
[sql]
SELECT
partner.state AS partner_state,
partner.position AS partner_position,
partner.own_points AS partner_own_points,
partner.net_points AS partner_net_points,
partner.net_admin AS partner_net_admin,
partner.team_leader AS partner_team_leader,
partner.bazaar_owner AS partner_bazaar_owner,
partner.bazaar_extractor AS partner_bazaar_extractor,
partner.desired_team_leader AS partner_desired_team_leader,
partner.desired_bazaar_owner AS partner_desired_bazaar_owner,
partner.desired_bazaar_extractor AS partner_desired_bazaar_extractor,
partner.visible AS partner_visible,
partner.sms_credits AS partner_sms_credits,
partner.id AS partner_id,
partner.external_id AS partner_external_id,
partner.subject_id AS partner_subject_id,
partner.sponsor_id AS partner_sponsor_id,
partner.team_id AS partner_team_id,
partner.bazaar_id AS partner_bazaar_id,
partner.higher_bazaar_id AS partner_higher_bazaar_id
FROM partner
WHERE partner.id IN (...)
[python]
joinedload(Partner.subject, innerjoin=True),
joinedload(Partner.subject, Subject.subject_personal, innerjoin=True),
joinedload(Partner.subject, Subject.subject_personal, SubjectPersonal.address,
innerjoin=True),
corresponds to
[sql]
SELECT
subject.created_at AS subject_created_at,
subject.id AS subject_id,
subject.subject_personal_id AS subject_subject_personal_id,
subject.subject_corporate_id AS subject_subject_corporate_id,
subject.creator_id AS subject_creator_id,
anon_1.partner_subject_id AS anon_1_partner_subject_id,
address_1.street AS address_1_street,
address_1.postal_code AS address_1_postal_code,
address_1.city AS address_1_city,
address_1.country AS address_1_country,
address_1.id AS address_1_id,
subject_personal_1.title_prefix AS subject_personal_1_title_prefix,
subject_personal_1.title_suffix AS subject_personal_1_title_suffix,
subject_personal_1.first_name AS subject_personal_1_first_name,
subject_personal_1.last_name AS subject_personal_1_last_name,
subject_personal_1.identification_number AS
subject_personal_1_identification_number,
subject_personal_1.birth_date AS subject_personal_1_birth_date,
subject_personal_1.mobile_personal AS subject_personal_1_mobile_personal,
subject_personal_1.phone_personal AS subject_personal_1_phone_personal,
subject_personal_1.phone_home AS subject_personal_1_phone_home,
subject_personal_1.phone_work AS subject_personal_1_phone_work,
subject_personal_1.mobile_work AS subject_personal_1_mobile_work,
subject_personal_1.email_personal AS subject_personal_1_email_personal,
subject_personal_1.email_work AS subject_personal_1_email_work,
subject_personal_1.email_home AS subject_personal_1_email_home,
subject_personal_1.comment AS subject_personal_1_comment,
subject_personal_1.id AS subject_personal_1_id,
subject_personal_1.external_id AS subject_personal_1_external_id,
subject_personal_1.address_id AS subject_personal_1_address_id
FROM
(
SELECT partner.subject_id AS partner_subject_id
FROM partner
WHERE partner.id IN (...)
) AS anon_1
JOIN subject ON anon_1.partner_subject_id = subject.id
JOIN subject_personal AS subject_personal_1 ON subject.subject_personal_id =
subject_personal_1.id
JOIN address AS address_1 ON subject_personal_1.address_id = address_1.id
ORDER BY anon_1.partner_subject_id
Q: I thought joinedload should be part of the MAIN query, or not?
[python]
subqueryload(Partner.subject, Subject.contacts),
corresponds to
[sql]
SELECT
contact.public_note AS contact_public_note,
contact.private_note AS contact_private_note,
contact.timestamp AS contact_timestamp,
contact.id AS contact_id,
contact.contact_personal_id AS contact_contact_personal_id,
contact.contact_corporate_id AS contact_contact_corporate_id,
contact.subject_id AS contact_subject_id,
contact.parent_id AS contact_parent_id,
subject_1.id AS subject_1_id
FROM
(
SELECT partner.subject_id AS partner_subject_id
FROM partner
WHERE partner.id IN (...)
) AS anon_1
JOIN subject AS subject_1 ON anon_1.partner_subject_id = subject_1.id
JOIN contact ON subject_1.id = contact.subject_id
ORDER BY subject_1.id
[python]
subqueryload(Partner.subject, Subject.contacts, Contact.contact_personal),
corresponds to
[sql]
SELECT
contact_personal.title_prefix AS contact_personal_title_prefix,
contact_personal.title_suffix AS contact_personal_title_suffix,
contact_personal.first_name AS contact_personal_first_name,
contact_personal.last_name AS contact_personal_last_name,
contact_personal.identification_number AS
contact_personal_identification_number,
contact_personal.birth_date AS contact_personal_birth_date,
contact_personal.id AS contact_personal_id,
contact_personal.birth_place_address_id AS
contact_personal_birth_place_address_id,
contact_personal.contact_address_id AS contact_personal_contact_address_id,
contact_personal.permanent_address_id AS
contact_personal_permanent_address_id,
contact_1.contact_personal_id AS contact_1_contact_personal_id
FROM
(
SELECT partner.subject_id AS partner_subject_id
FROM partner
WHERE partner.id IN (...)
) AS anon_1
JOIN subject AS subject_1 ON anon_1.partner_subject_id = subject_1.id
JOIN contact AS contact_1 ON subject_1.id = contact_1.subject_id
JOIN contact_personal ON contact_1.contact_personal_id = contact_personal.id
ORDER BY contact_1.contact_personal_id
NOTE: This, in theory, should be part of the previous subqueryload.
[python]
subqueryload(Partner.subject, Subject.contacts, Contact.contact_personal,
ContactPersonal.contact_address),
corresponds to
[sql]
SELECT
address.street AS address_street,
address.postal_code AS address_postal_code,
address.city AS address_city,
address.country AS address_country,
address.id AS address_id,
contact_personal_1.contact_address_id AS
contact_personal_1_contact_address_id
FROM
(
SELECT partner.subject_id AS partner_subject_id
FROM partner
WHERE partner.id IN (...)
) AS anon_1
JOIN subject AS subject_1 ON anon_1.partner_subject_id = subject_1.id
JOIN contact AS contact_1 ON subject_1.id = contact_1.subject_id
JOIN contact_personal AS contact_personal_1 ON contact_1.contact_personal_id
= contact_personal_1.id
JOIN address ON contact_personal_1.contact_address_id = address.id
ORDER BY contact_personal_1.contact_address_id
NOTE: This, in theory, should be also part of the previous subqueryload.
[python]
subqueryload(Partner.subject, Subject.contacts, Contact.contact_personal,
ContactPersonal.permanent_address),
corresponds to
[sql]
SELECT
address.street AS address_street,
address.postal_code AS address_postal_code,
address.city AS address_city,
address.country AS address_country,
address.id AS address_id,
contact_personal_1.permanent_address_id AS
contact_personal_1_permanent_address_id
FROM
(
SELECT partner.subject_id AS partner_subject_id
FROM partner
WHERE partner.id IN (...)
) AS anon_1
JOIN subject AS subject_1 ON anon_1.partner_subject_id = subject_1.id
JOIN contact AS contact_1 ON subject_1.id = contact_1.subject_id
JOIN contact_personal AS contact_personal_1 ON contact_1.contact_personal_id
= contact_personal_1.id
JOIN address ON contact_personal_1.permanent_address_id = address.id
ORDER BY contact_personal_1.permanent_address_id
[python]
subqueryload_all(Partner.subject, Subject.tag_subjects, TagSubject.tag),
corresponds to
[sql]
SELECT
tag_subject.id AS tag_subject_id,
tag_subject.tag_id AS tag_subject_tag_id,
tag_subject.subject_id AS tag_subject_subject_id,
subject_1.id AS subject_1_id
FROM
(
SELECT partner.subject_id AS partner_subject_id
FROM partner
WHERE partner.id IN (...)
) AS anon_1
JOIN subject AS subject_1 ON anon_1.partner_subject_id = subject_1.id
JOIN tag_subject ON subject_1.id = tag_subject.subject_id
ORDER BY subject_1.id
Q: Why the SQL does NOT contain Tag.* info?
[python]
subqueryload_all(Partner.partner_regions),
corresponds to
[sql]
SELECT
partner_region.personal_number AS partner_region_personal_number,
partner_region.hrk_admin AS partner_region_hrk_admin,
partner_region.srk_admin AS partner_region_srk_admin,
partner_region.id AS partner_region_id,
partner_region.partner_id AS partner_region_partner_id,
partner_region.region_id AS partner_region_region_id,
partner_region.office_id AS partner_region_office_id,
anon_1.partner_id AS anon_1_partner_id
FROM
(
SELECT partner.id AS partner_id
FROM partner
WHERE partner.id IN (...)
) AS anon_1
JOIN partner_region ON anon_1.partner_id = partner_region.partner_id
ORDER BY anon_1.partner_id
Your thoughts on this? Do you see anything suspicious?
BTW the new system for 0.9 looks very nice!
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.
For more options, visit https://groups.google.com/groups/opt_out.
