I have a question about implicit vs explicit joins in SA.
I have three tables with an odd relationship (legacy DB issues).
Assume tables 'accounts' and 'account_config' and a secondary table
'account_to_config'. The true nature of the relation is 1:1, however
(again, it's an inherited schema...)
I've defined the relationship thusly:
On the Account object mapper:
'config': relation(
AccountConfig,
secondary=account_to_config,
uselist=False,
single_parent=True,
cascade="all,delete-orphan",
)
When I access the 'config' attribute of a loaded Account instance, the
SQL takes the following approximate form:
SELECT <bunch of stuff>
FROM account_config, account_to_config
WHERE
%(param_1)s = account_to_config.account_id
AND
account_config.id = account_to_config.account_config_id
This is an implicit join, and is equivalent to:
SELECT <bunch of stuff>
FROM account_config
JOIN account_to_config
ON account_config.id = account_to_config.account_config_id
WHERE
%(param_1)s = account_to_config.account_id
My understanding is that some databases prefer the explicit JOIN
syntax from an optimization POV, and of course the explicit JOIN
syntax is ANSI SQL. Googlin' suggests that the ANSI syntax is
preferred (perhaps even required) by some engines, but of course
that's an unreliable source.
I am curious why SA chooses to do it this way instead of with a JOIN.
I don't think it is /WRONG/, I'm merely curious.
--
Jon
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
