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 <lmer...@gmail.com> 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 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.