When you say this:
@declared_attr
def parent(cls):
...
remote_side=[cls.friday_id, cls.user_hash],
Delcarative calls "parent" within the same configuration phase that it calls
"user_hash" for the first time to generate a new Column. So when you call
upon cls.user_hash, you're calling the UserMixin.user_hash classmethod, getting
at a newly generated Column, which is then stuffed into the remote_side
argument. Declarative calls cls.user_hash itself to get a Column which is the
one it actually applies to the class. So there are two user_hash columns
floating around. If you stick a print statement into the user_hash
@declared_attr you can see this.
Just place that remote_side into quotes, so that it is evaluated at mapper
configuration time, long after People.user_hash has been established as a
Column on People:
@declared_attr
def parent(cls):
return relationship(
'People',
remote_side="[People.friday_id, People.user_hash]",
post_update=True)
Also note that the pattern at play here is the "composite self referential key
with column pointing to itself". This use case is not fully functional until
version 0.8, in 0.7 and earlier you'll see operations like join() and
joinedload() failing to alias the "user_hash=user_hash" clause correctly.
See
http://www.sqlalchemy.org/trac/wiki/08Migration#Rewrittenrelationshipmechanics .
On Jun 18, 2012, at 3:40 AM, Fayaz Yusuf Khan wrote:
> The attached script generates an inefficient query at the end:
>
> SELECT `People`.friday_id AS `People_friday_id`, `People`.parent_id AS
> `People_parent_id`, `People`.user_hash AS `People_user_hash`
> FROM `People`
> WHERE `People`.friday_id = %s AND `People`.user_hash = `People`.user_hash
>
> Please note the user_hash = user_hash clause being generated there. This
> query takes a heavy toll on the datastore and has become a huge bottleneck
> in our application.
>
> This is the core ORM configuration:
>
> class User(Base):
>
> hash = Column(String(64), primary_key=True)
>
>
> class UserMixin(object):
>
> @declared_attr
> def user_hash(cls):
> return Column(String(64), ForeignKey('User.hash'), primary_key=True)
>
>
> class People(UserMixin, Base):
>
> friday_id = Column(BigInteger, primary_key=True, nullable=False,
> autoincrement=False)
>
> # This line mysteriously fixes the query
> #user_hash = Column(String(64), ForeignKey('User.hash'),
> # primary_key=True)
>
> parent_id = Column(BigInteger)
>
> @declared_attr
> def parent(cls):
> return relationship(
> 'People', remote_side=[cls.friday_id, cls.user_hash],
> post_update=True)
>
> __table_args__ = (
> ForeignKeyConstraint(
> ['parent_id', 'user_hash'],
> ['People.friday_id', 'People.user_hash'],
> ondelete='CASCADE'),)
>
> As shown in the comments, if I remove the UserMixin, the query becomes sane.
>
> --
> Fayaz Yusuf Khan
> Cloud architect, Dexetra SS, India
> fayaz.yusuf.khan_AT_gmail_DOT_com, fayaz_AT_dexetra_DOT_com
> +91-9746-830-823
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
> <test.py>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
