a polymorphic association is hard. that's why I have three examples of them and soon a fourth. Though they are a subset of a larger batch of "tricks" that I've been using in my own work with declarative for the past year to automate lots of different kinds of patterns, perhaps there's a learning curve but once three or four techniques are mastered they come pretty easily.
On Feb 13, 2011, at 4:35 PM, Martijn Moeling wrote: > Michael, > > I looked at the code and I can not say more than that its very interesting, I > have to see how it works and more importantly how It fits into my objects but > it seems clear enough to do so. > I really appreciate your work on SQLAlchemy and all the time you spend to > help us "users" out. > > Your solution is definitively one I could not have put together myself. > Although I have tried. SQLAlchemy is so powerful that it is hard to find "the > right options" for the job. > > Thank you again! > > Martijn > > On Feb 13, 2011, at 21:19 , Michael Bayer wrote: > >> >> On Feb 13, 2011, at 6:14 AM, Martijn Moeling wrote: >> >>> You are right in the misunderstood relation. >>> >>> I see the primary key in extra to be wrong, extra should have it's own I'd >>> column being an auto number. In extra it should be possible to have many >>> records pointing to 1 ext variant. Sorry for that. >>> >>> The extra, should also work with tables without a discriminator, there the >>> link should be made to table name which is in my case always >>> class.__name__ ...... On those tables, the relation needs to be different >>> since on of the "local" columns, discriminator is not present and it >>> somehow should be linked to __table name__ >> >> OK what you are trying to do is exactly a "polymorphic association". The >> technique of placing "tablename" in the table of "related" records, then >> using that "tablename" to indicate which parent table should be matched at >> query time, is a common, but IMHO relationally incorrect pattern. I >> blogged extensively about the Ruby on Rails approach, how to duplicate >> Rails' approach in SQLAlchemy, and then an alternate system which maintains >> referential integrity, four years ago at >> http://techspot.zzzeek.org/2007/05/29/polymorphic-associations-with-sqlalchemy/ >> . >> >> That's a really old example and we're into 0.7 now, so I've created a new >> version of the poly assoc example that uses declarative techniques and the >> association proxy, which is attached. I'm going to further fix up this >> example and add it to the distribution as a fourth example of polymorphic >> association, which is in examples/poly_assoc/. >> >> Additionally I'd like the association proxy to work more smoothly in queries >> so I've added ticket #2054 for some of those issues which weren't accounted >> for when we first added any(), contains() operators to the association proxy. >> >> >> >> >> >>> >>> It is all part of the ACL examples it talked about walker, where extra must >>> be seen as the ACL. That is where the mapper extension comes in.... >>> >>> It is getting a "all technologies" mixed in situation >>> >>> Verstuurd vanaf mijn iPad >>> >>> Op Feb 12, 2011 om 17:05 heeft "Michael Bayer" <mike...@zzzcomputing.com> >>> het volgende geschreven: >>> >>>> OK I can show you the version of your code that does most of this but >>>> there are some fundamental relational misunderstandings in this schema if >>>> I am interpreting correctly. >>>> >>>> Extra: >>>> >>>> tableid tablename >>>> ------- --------- >>>> 1 ext1 >>>> 2 ext1 >>>> 3 ext2 >>>> 4 ext2 >>>> 5 ext3 >>>> >>>> ext1: >>>> >>>> id discriminator (-> FK to Extra.tableid, Extra.tablename) >>>> -- ------------- >>>> 1 ext1 >>>> 2 ext1 >>>> 3 ext2 >>>> 4 ext2 >>>> 5 ext3 >>>> >>>> ext2: >>>> >>>> id >>>> -- >>>> 3 >>>> 4 >>>> >>>> ext3: >>>> >>>> id >>>> -- >>>> >>>> 5 >>>> >>>> given ext1 ID #3, discriminator "ext2" - how can more than one Extra row >>>> be referenced? Why is "extras" assumed to be one-to-many when it can only >>>> be many-to-one ? >>>> >>>> >>>> >>>> >>>> >>>> >>>> On Feb 12, 2011, at 9:57 AM, Martijn Moeling wrote: >>>> >>>>> This whole thing is driving me crazy, What I want: >>>>> >>>>> >>>>> >>>>> class Extra(Base): >>>>> __tablename__ = "extra" >>>>> # Primary key consists of two different columns !!! >>>>> tableId = Column(Integer, primary_key=true) >>>>> tablename = Column(Unicode(20), primary_key=True) >>>>> >>>>> info = Column(........) #Not relevant >>>>> >>>>> >>>>> >>>>> >>>>> class ex1(Base): >>>>> Id = Column(Integer, primary_key=True) >>>>> discriminator = Column(Unicode(20)) >>>>> >>>>> @declared_attr >>>>> def __tablename__(self): >>>>> return self.__name__.lower() >>>>> >>>>> @declared_attr >>>>> def __mapper_args__(self): >>>>> if self.__name__ == 'ext1': >>>>> return {'polymorphic_on': self.discriminator, >>>>> 'polymorphic_identity':unicode(self.__name__.lower()), >>>>> 'extension': FilePropertiesMapperExtension(), >>>>> 'batch' : False} >>>>> else: >>>>> return {'polymorphic_identity':unicode(self.__name__.lower()), >>>>> 'inherit_condition': self.Id == extra.Id, >>>>> #needed for something else in this config (multiple self >>>>> reference) >>>>> 'extension': FilePropertiesMapperExtension(), >>>>> #Needed for something else, not relevant for this sample >>>>> 'batch' : False} >>>>> # ,, ,, ,, ,, >>>>> >>>>> >>>>> # Set up foreignkey and relation to Extra.... >>>>> __table_args__ = (ForeignKeyConstraint(['discriminator', 'Id'], >>>>> ['extra.Table','extra.TableId']),{}) >>>>> >>>>> >>>>> extras = relation('Extra', cascade="all", lazy="dynamic" >>>>> backref="owner") >>>>> >>>>> >>>>> .... >>>>> .... >>>>> >>>>> >>>>> class ext2(ext1): >>>>> Id = Column(Integer,ForeignKey('ext1.Id'), >>>>> primary_key=True) >>>>> ...... >>>>> >>>>> >>>>> class ext3(ext1): >>>>> Id = Column(Integer,ForeignKey('ext1.Id'), >>>>> primary_key=True) >>>>> ..... >>>>> >>>>> >>>>> >>>>> >>>>> Now I want: >>>>> >>>>> Ext2 = ext2() >>>>> Extra_info = extra() >>>>> Ext2.extras.append(Extra_Info) >>>>> >>>>> >>>>> Ext2.discriminator should be "ext2" >>>>> Ext2.Id should be 1 for the first record >>>>> >>>>> Extra_Info should be created in the database, with its columns : id set >>>>> to the Ext2.id and tablename to Ext.discriminator ...... >>>>> Extra_Info.owner would point to Ext2 >>>>> >>>>> If Ext2 is deleted, all related extrainfo record would be delete too >>>>> >>>>> if one Extra_Info is deleted, Extra_Info.owner should stay in place as >>>>> well as all other related >>>>> >>>>> Extra is many to one polymorhic version of ext1 >>>>> >>>>> I hope this clarifies more what I want.... >>>>> >>>>> I really need the @declared_attr way of doing stuff and that is not >>>>> related to this question but might influence this question so I left it >>>>> in...... >>>>> >>>>> Martijn >>>>> >>>>> >>>>> On Feb 10, 2011, at 18:13 , Michael Bayer wrote: >>>>> >>>>>> >>>>>> On Feb 10, 2011, at 4:20 AM, Martijn Moeling wrote: >>>>>> >>>>>>> Another small thing: >>>>>>> >>>>>>> I took a look at: >>>>>>> >>>>>>> ForeignKeyConstraint(['invoice_id', 'ref_num'], ['invoice.invoice_id', >>>>>>> 'invoice.ref_num']) >>>>>>> >>>>>>> Now for Polymorphic tables: >>>>>>> >>>>>>> >>>>>>> in baseclass: >>>>>>> >>>>>>> baseclass.discriminator happens to be the __tablename__ of the >>>>>>> polymorphic >>>>>>> >>>>>>> ForeignKeyConstraint('['baseclass.disciminator', baseclass.Id'], >>>>>>> ['someotherclass.tablename','someotherclass.tableId'] >>>>>>> relationship('someotherclass', backref=baseclass, cascade="all", >>>>>>> lazy="dynamic") >>>>>>> >>>>>>> >>>>>>> in someotheclass: >>>>>>> >>>>>>> tablename = column(Unicode(20), primary_key=True) >>>>>>> tableId = column(Integer, primary_key=True) >>>>>>> >>>>>>> seems Ok to me. >>>>>>> >>>>>>> Now I need to make someotherclass work with non-polymorphic tables too!! >>>>>>> >>>>>>> anotherclass: >>>>>>> Id = column(Integer, primary_key=True) >>>>>>> ForeignKeyConstaint('[anotherclass.__tablename__,'anotherclass.Id'],['someotherclass.tablename','someotherclass.tableId']) >>>>>>> relation('someotherclass', backref=baseclass, cascade="all", >>>>>>> lazy="dynamic") >>>>>>> >>>>>>> Is there any way to get this working without configuring it as >>>>>>> polymorphic an do no Inhiritance, I do not want each anotherclass >>>>>>> record to have a column discriminator with its own tablename! >>>>>>> >>>>>>> or can I use anotherclass.__tablename__ in the ForeignKeyConstaint? >>>>>>> >>>>>>> This has to do with the someotherclass being the "ACL" I talked about >>>>>>> in a previous post if that gives extra info. I am trying to implement >>>>>>> the MapperExtension.before_append where I need to refer to the "ACL" >>>>>>> records in a way like: >>>>>>> >>>>>>> for A in instance.ACL: >>>>>>> ..... >>>>>> >>>>>> yeah, sorry, this use case continues to be 98% opaque to me. I don't >>>>>> understand what you mean by "make someotherclass work with >>>>>> non-polymorphic tables", a class is mapped in just one way, either with >>>>>> or without a discriminator column. A single class can't be mapped in >>>>>> both ways. If there's no discriminator, there's just one class that >>>>>> can be used for returned rows. >>>>>> >>>>>> If you could create a small test that illustrates a mapping and an >>>>>> expected result, perhaps I can attempt to find a way to get the ORM >>>>>> behavior you're looking for. >>>>>> >>>>>> Your mapper extension would continue to be used normally with a >>>>>> "dynamic" relationship since it uses query(cls) just like a regular >>>>>> query. >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> 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. >>>>>> >>>>> >>>>> -- >>>>> 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. >>>>> >>>> >>>> -- >>>> 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. >>>> >>> >>> -- >>> 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. >>> >> >> <martijnmoeling.py>-- >> 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. >> > > -- > 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. > -- 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.
