On Monday, February 24, 2014 1:12:59 PM UTC-8, Luke wrote:
>
>
>
> On Monday, February 24, 2014 12:55:16 PM UTC-8, Michael Bayer wrote:
>>
>>
>> On Feb 24, 2014, at 2:41 PM, Luke <[email protected]> wrote:
>>
>> Hi,
>>
>>
>>
>> class Response(Writing):
>> # All responses are associated with Writing. Writing/Articles may have
>> # several responses
>> #
>> __tablename__ = 'response'
>> id = Column('id', ForeignKey('writing.id'), primary_key=True)
>> respondee_id = Column('respondee', ForeignKey('article.id'),
>> nullable=False)
>>
>> # So, No, I don't know what I'm doing here...
>> respondee = relationship(
>> 'Response', backref='respondant',
>> primaryjoin=('Writing.respondant' == 'Response.respondee'))
>> __mapper_args__ ={
>> 'polymorphic_identity': 'response', }
>>
>>
>> 1. for the syntax on primaryjoin as a string, it has to be all one string:
>>
>> primaryjoin = “Writing.id == Response.respondee_id”
>>
>> see the examples at:
>>
>>
>> http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-primaryjoin
>>
>> http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#configuring-relationships
>>
>> 2. The primaryjoin is between columns, which are accessed using the
>> attribute name given, such as Writing.id. not the name of a relationship
>> (respondent).
>>
>> 3. the relationship joins two tables between primary key columns and
>> foreign key columns. In this case, you’re saying you’d like
>> “response.respondee” to refer to “article.id”, so you’d be setting up
>> primary join in that way…..but
>>
>> 4. ..the mapping doesn’t seem to make sense in that regard, you have
>> Response linked to Response, and not Article. If Article is just one kind
>> of Writing, and Response belongs to any kind of Writing, it would be:
>>
>> class Response(Writing):
>> __tablename__ = 'response'
>> id = Column('id', ForeignKey('writing.id'), primary_key=True)
>> respondee_id = Column(ForeignKey('writing.id'), nullable=False)
>>
>> writing = relationship(Writing,
>> remote_side=Writing.id,
>> backref="responses",
>> primaryjoin=Writing.id == respondee_id)
>>
>> __mapper_args__ = {'polymorphic_identity': 'response',
>> 'inherit_condition': id == Writing.id}
>>
>> inherit condition is needed as “response” now has two ways to link
>> directly to “writing”.
>>
>> 5. On the topic of “not an expert in SQL”, I will say that this kind of
>> design where “everything inherits from joined-table X” is extremely common
>> for people I see moving from a pure OO background to beginning to mix SQL
>> into their modeling. It’s not “wrong” per se and can be a useful design,
>> but in my experience is overused, and it also leads to queries that are
>> very complicated on the render side and often not very performant. You
>> should consider very carefully how many columns will actually be on
>> “Writing” as well as how many kinds of “sub-tables” will really be needed
>> to import these columns on “writing”; additionally, you should consider how
>> much of a need you will have to query across all kinds of “Writing” objects
>> at the same time. In practice, this use case is rare; your app will either
>> need to see lists of Articles, or it will need to see the list of Responses
>> given a parent Writing. If Writing has only a handful of columns, it
>> would be much more efficient and simple from both a storage and a querying
>> perspective to just have a “response” table, an “article” table, and then a
>> few columns on both that just happen to be the same. From the object
>> modeling side, you can keep Response and Article as subclasses of Writing,
>> however you can have Writing itself be a non-mapped mixin or __abstract__
>> class. The mapping becomes extremely simple and from a SQL perspective
>> much more succinct and performant:
>>
>> class Writing(Base):
>> __abstract__ = True
>> id = Column(Integer, primary_key=True) # gets copied out to
>> subclasses
>>
>> class Article(Writing):
>> __tablename__ = 'article'
>>
>> class Response(Writing):
>> __tablename__ = 'response'
>> respondee_id = Column(ForeignKey('article.id'), nullable=False)
>> writing = relationship(Article, backref="responses")
>>
>>
> Michael,
>
> Thank you for the care you put into your support here and on
> stackoverflow. I will consider your advice carefully. I had moved to this
> more complex, inherited design after implementing simple, independent
> tables (as you suggested). And you have confirmed that it's probably not
> worth the trouble. I am concerned that the more complex my mapped classes
> are, the more complex, and thus slow, my queries will be. I'm not even to
> the point where I have thought about tuning queries or caching. I am not a
> trained programmer and I have probably bitten off more than I can chew.
> But, in any case, that's not your concern.
>
> Thank you again for the help.
>
> Luke
>
Michael,
I am continuing to get the same AmbiguousForeignKeysError with your first
suggestion. Your simpler suggestion, using an abstract Base, does not seem
to want to *easily* handle a many to many relationship in the base class.
(Authors <-> Writing) I don't want you to think I'm not pouring over the
docs, SO, and indeed several of your conference presentations. I have. But
there is so much going on here. What I am looking for is either:
(1) A reason this primary join isn't working for me.
(2) A way to have the Author <-> Writing relationship with a Writing
abstract base (I tried `declared_attr`).
Do you have a moment to look over a simplified but more complete example of
what I am trying to achieve? If not, I'll try to re-implement with
non-inherited tables or the abstract base again.
Thanks again.
- Luke
author_to_writing = Table(
'author_to_writing', Base.metadata,
Column('writing_id', Integer, ForeignKey('writing.id')),
Column('author_id', Integer, ForeignKey('author.id')))
class Writing(Base):
__tablename__ = 'writing'
id = Column('id', Integer, primary_key=True)
type = Column(String)
__mapper_args__ = {
'polymorphic_identity': 'writing',
'polymorphic_on': type
}
authors = relationship('Author', secondary=author_to_writing,
backref=backref('author', lazy='dynamic'))
class Article(Writing):
__tablename__ = 'article'
id = Column('id', ForeignKey('writing.id'), primary_key=True)
__mapper_args_ = {'polymorphic_identity': 'article'}
class Response(Writing):
__tablename__ = 'response'
id = Column('id', ForeignKey('writing.id'), primary_key=True)
__mapper_args_ = {
'polymorphic_identity': 'response',
'inherit_condition': id == Writing.id}
respondee_id = Column(ForeignKey('writing.id'))
writing = relationship(
Writing,
remote_side=Writing.id,
backref='responses',
primaryjoin=Writing.id == respondee_id)
class Author(Base):
__tablename__ = 'author'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
--
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.
