On 4/1/15 10:28 AM, Pierre B wrote: > Here's a simple visual of the schema
OK, so that's called a polymorphic foreign key. SQLAlchemy doesn't have first class support for this concept because it's relationally incorrect, but there is an example at http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/generic_associations/generic_fk.html which shows one way to produce this effect. The key aspects to this in reference to your model attempts are that there are no ForeignKey objects; objects like Column and ForeignKey are schema-level objects, and if you construct one, that implies it exists in the schema. That's why you can't make two Column objects with the same name pointing to the same table, and this is what I'm referring to when I say that the schema has to be considered when building out these declarations. The techniques to make the relationship here involve using the "primaryjoin" argument to establish how the tables join directly, as well as the "foreign()" annotation and/or "foreign_keys" argument which you can see used in the example; that is, how the tables join is constructed using all ORM constructs and not schema-level constructs. > > <https://i.imgur.com/TaC2V6b.png> > > There are no foreign key constraints in the database schema, id1 and > id2 are just stored there, a type column is used to retrieve records > e.g type equals B for an association between RightB and ReftB and > equals A between RightA and LeftA. That is why I'm trying to set a > default value for the type column so I don't have to deal with that > junction table when inserting records. > > > > On Wednesday, April 1, 2015 at 4:11:03 PM UTC+2, Michael Bayer wrote: > > > > On 4/1/15 4:55 AM, Pierre B wrote: >> Unfortunately I'm inheriting the relational model from an old >> application. I have dozens of tables using a single junction >> table for associations. >> I can not completely redesign my relational model because it >> needs to be compatible with the old application. > I was asking no such thing. I only ask that you consider the > relational model when building *new* elements of the > application. If these models are in fact mapping to an existing > schema, I find it surprising that your existing database schema > includes *two* foreign key constraints present on each of > people4l2.id1 and people4l2.id2, constraining each column to both > left1.id/left2.id <http://left1.id/left2.id> and > right1.id/right2.id <http://right1.id/right2.id>. > > > > >> At this point, I think my best option is setting up table >> inheritance at the database level (database is Postgresql) and >> migrating records into children tables. Minimal code refactoring >> would be involved in the old application and it would be possible >> to use the association object pattern. >> >> On Tuesday, March 31, 2015 at 8:05:19 PM UTC+2, Michael Bayer wrote: >> >> >> >> Pierre B <[email protected]> wrote: >> >> > I tried using the association object pattern before but >> can't get it to work because I use the same id1 and id2 >> columns for all foreign keys and I'm not able to override >> them in the sub-classes ("conflicts with existing column" >> error). >> > class MyClass(HasSomeAttribute, db.Model): >> > __tablename__ = 'people4l2' >> > id = db.Column(db.Integer, primary_key=True) >> > >> > class MySubClass1(MyClass): >> > right1_id = db.Column('id2', db.Integer, >> ForeignKey('right1.id <http://right1.id>')) >> > left1_id = db.Column('id1', db.Integer, >> ForeignKey('left1.id <http://left1.id>')) >> > >> > class MySubClass2(MyClass): >> > right2_id = db.Column('id2', db.Integer, >> ForeignKey('right2.id <http://right2.id>')) >> > left2_id = db.Column('id1', db.Integer, >> ForeignKey('left2.id <http://left2.id>’)) >> >> That’s because you do not have a __tablename__ for these >> subclasses, so when >> you put a column on the subclass, that is physically a column >> on the >> ‘people4l2’ table; the names cannot be conflicting. Also, it >> is not possible >> to have a column named “people4l2.id2” which is in some cases >> a foreign key >> to “right1.id <http://right1.id>” and in other cases to >> “right2.id <http://right2.id>”. >> >> This probably all seems very complicated if you only think of >> it in terms of >> a Python object model. That’s why it is essential that you >> design your >> database schema in terms of database tables, and how those >> tables will work >> within a purely relational model, without Python being >> involved, first. >> >> For simple cases, the design of the relational model and the >> object model >> are so similar that this explicit step isn’t necessary, but >> once the goals >> become a little bit divergent between relational and object >> model, that’s >> when the relational model has to be developed separately, up >> front. This is >> the essence of how SQLAlchemy works, which becomes apparent >> the moment you >> get into models like these which are typically impossible on >> most other >> ORMs, since most ORMs do not consider design of the >> relational model as >> separate from the object model. >> >> The tradeoff here is basically between “more work with >> SQLAlchemy” vs. >> “not possible at all with other ORMs” :) >> >> The relational model is the more rigid part of the system >> here, so you have to >> work that part out first; then determine how you want to map >> the Python >> object model on top of the relational model. >> >> > On Tuesday, March 31, 2015 at 4:29:52 PM UTC+2, Michael >> Bayer wrote: >> > >> > >> > Pierre B <[email protected]> wrote: >> > >> > > Here's my use case: >> > > right1 = Right() >> > > right.left = Left() >> > > >> > > right2 = Right2() >> > > right2.left = Left2() >> > > >> > > db.session.add(right) // automatically create the >> junction using MySubClass1 and set the type field to 1 >> > > db.session.add(right2) // automatically create the >> junction using MySubClass1 and set the type field to 2 >> > > db.session.commit() >> > > >> > > Basically I have a junction table associating a bunch of >> different tables in my model. >> > > I want to abstract that mechanism using relationships and >> polymorphism so that I don't have to deal with that junction >> table while coding. >> > > The relationships I created allow me to not have to deal >> with it while selecting records but I can't get it to set the >> type field while inserting records. >> > >> > OK, you are using the association object pattern. You >> cannot use “secondary” >> > in the way that you are doing here. You need to map a >> relationship to >> > MySubClass1 explicitly. To reduce verbosity, you’d then >> apply the >> > association proxy pattern. Without association proxy, your >> association of >> > right and left will be something like: >> > >> > right2 = Right2() >> > right2.left_association = MySubClass1() >> > right2.left_association.left = Left2() >> > >> > the association proxy then allows for MySubClass1() to be >> called >> > automatically and you can refer to “right2.left” directly. >> > >> > Start with: >> > >> >> http://docs.sqlalchemy.org/en/rel_0_9/orm/basic_relationships.html#association-object >> >> <http://docs.sqlalchemy.org/en/rel_0_9/orm/basic_relationships.html#association-object> >> >> > >> > make that work completely, with the more verbose use pattern. >> > >> > then when that is totally working and understood, then move >> onto association >> > proxy: >> > >> > >> >> http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html >> >> <http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/associationproxy.html> >> >> > >> > >> > >> > > On Tuesday, March 31, 2015 at 4:11:51 PM UTC+2, Michael >> Bayer wrote: >> > > >> > > Pierre B <[email protected]> wrote: >> > > >> > > > I made a type in the Right model, here are the models >> again: >> > > >> > > if you’re referring to the behavior of Right.left when >> you use it in a >> > > query, such as query(Right).join(Right.left), then the >> “default” value of a >> > > Column object has no interaction there. >> > > >> > > it seems like you probably want to do something very >> simple here but I’m not >> > > getting enough information on what that is. If you could >> illustrate the >> > > usage of the objects that you are looking for, that would >> help. >> > > >> > > > >> > > >> > > >> > > >> > > > class HasSomeAttribute(object): >> > > > @declared_attr.cascading >> > > > def type(cls): >> > > > if has_inherited_table(cls): >> > > > if cls.__name__ == 'MySubClass1': >> > > > return db.Column(db.Integer, default=1) >> > > > else: >> > > > return db.Column(db.Integer, default=2) >> > > > else: >> > > > return db.Column(db.Integer, default=0) >> > > > >> > > > class MyClass(HasSomeAttribute, db.Model): >> > > > __tablename__ = 'people4l2' >> > > > id = db.Column(db.Integer, primary_key=True) >> > > > id1 = db.Column(db.Integer) >> > > > id2 = db.Column(db.Integer) >> > > > >> > > > class MySubClass1(MyClass): >> > > > pass >> > > > >> > > > class MySubClass2(MyClass): >> > > > pass >> > > > >> > > > class Right(db.Model): >> > > > id = db.Column(db.Integer, primary_key=True) >> > > > left = relationship( >> > > > 'Left', >> > > > secondary= MySubClass1.__table__, >> > > > primaryjoin='and_(MySubClass1.type == 802, >> MySubClass1.id2 == Right.id)', >> > > > secondaryjoin='and_(MySubClass1.type == 802, >> MySubClass1.id1 == Left.id)' >> > > > ) >> > > > >> > > > class Left(db.Model): >> > > > id = db.Column(db.Integer, primary_key=True) >> > > > >> > > > >> > > > On Tuesday, March 31, 2015 at 12:12:35 PM UTC+2, Pierre >> B wrote: >> > > > Hi Michael, >> > > > >> > > > Thank you for your response. >> > > > Unfortunately I have already tried to use the __init__ >> function/catch the init event but I am only referencing the >> sub classes in a relationship which does not seem to actually >> instantiate classes because the __init__ is never called/init >> event is never fired. >> > > > Here is a simple version of my models. >> > > > >> > > > class HasSomeAttribute(object): >> > > > @declared_attr.cascading >> > > > def type(cls): >> > > > if has_inherited_table(cls): >> > > > if cls.__name__ == 'MySubClass1': >> > > > return db.Column(db.Integer, default=1) >> > > > else: >> > > > return db.Column(db.Integer, default=2) >> > > > else: >> > > > return db.Column(db.Integer, default=0) >> > > > >> > > > class MyClass(HasSomeAttribute, db.Model): >> > > > __tablename__ = 'people4l2' >> > > > id = db.Column(db.Integer, primary_key=True) >> > > > id1 = db.Column(db.Integer) >> > > > id2 = db.Column(db.Integer) >> > > > >> > > > class MySubClass1(MyClass): >> > > > pass >> > > > >> > > > class MySubClass2(MyClass): >> > > > pass >> > > > >> > > > class Right(db.Model): >> > > > id = db.Column(db.Integer, primary_key=True) >> > > > subclass_attr = relationship( >> > > > 'Contact', >> > > > secondary= MySubClass1.__table__, >> > > > primaryjoin='and_(MySubClass1.type == 802, >> MySubClass1.id2 == Right.id)', >> > > > secondaryjoin='and_(MySubClass1.type == 802, >> MySubClass1.id1 == Left.id)' >> > > > ) >> > > > >> > > > class Left(db.Model): >> > > > id = db.Column(db.Integer, primary_key=True) >> > > > >> > > > MyClass is used as a junction table for a bunch of >> different relationships, the type field is used to >> differentiate the relationships. >> > > > >> > > > >> > > > On Monday, March 30, 2015 at 5:26:30 PM UTC+2, Michael >> Bayer wrote: >> > > > >> > > > >> > > > Pierre B <[email protected]> wrote: >> > > > >> > > > > Hi all, >> > > > > >> > > > > I'm ultimately trying to have different default >> values for the same column. Following the documentation, the >> @declared_attr.cacading decorator seems to be the best approach. >> > > > > Here's my code: >> > > > > class HasSomeAttribute(object): >> > > > > @declared_attr.cascading >> > > > > def type(cls): >> > > > > if has_inherited_table(cls): >> > > > > if cls.__name__ == 'MySubClass1': >> > > > > return db.Column(db.Integer, default=1) >> > > > > else: >> > > > > return db.Column(db.Integer, default=2) >> > > > > else: >> > > > > return db.Column(db.Integer, default=0) >> > > > > >> > > > > class MyClass(HasSomeAttribute, db.Model): >> > > > > __tablename__ = 'people4l2' >> > > > > id = db.Column(db.Integer, primary_key=True) >> > > > > >> > > > > class MySubClass1(MyClass): >> > > > > pass >> > > > > >> > > > > class MySubClass2(MyClass): >> > > > > pass >> > > > > >> > > > > I iterated quite a few times over this but I'm >> systematically getting this error: >> > > > > ArgumentError: Column 'type' on class <class >> '__main__.MySubClass1'> conflicts with existing column >> 'people4l2.type’ >> > > > >> > > > this mapping illustrates MySubClass1 and MySubClass2 as >> both sharing the >> > > > same table “people4l2”, as they have no __tablename__ >> attribute, so there >> > > > can only be one “type” column. So in this case it is >> not appropriate to use >> > > > cascading in exactly this way, as MyClass already has a >> “type” column, and >> > > > that gets attached to the “people4l2” table and that’s >> it; there can be no >> > > > different “type” column on MySubClass1/MySubClass2. >> > > > >> > > > If you’d like “type” to do something different based on >> which class is being >> > > > instantiated, this is an ORM-level differentiation. Use >> either the >> > > > constructor __init__() to set it or use the init() event >> > > > >> >> (http://docs.sqlalchemy.org/en/rel_0_9/orm/events.html?highlight=event%20init#sqlalchemy.orm.events.InstanceEvents.init >> >> <http://docs.sqlalchemy.org/en/rel_0_9/orm/events.html?highlight=event%20init#sqlalchemy.orm.events.InstanceEvents.init>). >> >> > > > >> > > > OTOH if “type” is actually the “polymoprhic >> discriminator”, which is what >> > > > this looks like, then you’d be looking to just set up >> “type” as the >> > > > “polymorphic_on” column and set up the “1”, “2”, “0” as >> the polymorphic >> > > > identity (see >> > > > >> >> http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#single-table-inheritance >> >> <http://docs.sqlalchemy.org/en/rel_0_9/orm/inheritance.html#single-table-inheritance> >> >> > > > for a simple example). >> > > > >> > > > >> > > > -- >> > > > 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 >> <http://groups.google.com/group/sqlalchemy>. >> > > > For more options, visit >> https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. >> > > >> > > -- >> > > 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 >> <http://groups.google.com/group/sqlalchemy>. >> > > For more options, visit >> https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. >> > >> > -- >> > 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 >> <http://groups.google.com/group/sqlalchemy>. >> > For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. >> >> -- >> 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] <javascript:>. >> To post to this group, send email to [email protected] >> <javascript:>. >> Visit this group at http://groups.google.com/group/sqlalchemy >> <http://groups.google.com/group/sqlalchemy>. >> For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. > > -- > 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] > <mailto:[email protected]>. > To post to this group, send email to [email protected] > <mailto:[email protected]>. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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/d/optout.
