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. 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] <javascript:>> 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')) > > left1_id = db.Column('id1', db.Integer, ForeignKey('left1.id')) > > > > class MySubClass2(MyClass): > > right2_id = db.Column('id2', db.Integer, ForeignKey('right2.id')) > > left2_id = db.Column('id1', db.Integer, ForeignKey('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” and in other cases to “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 > > > > > 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 > > > > > > > > > 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). > > > > > > > > > > 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 > > > > > 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. > > > > 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. > > > > -- > > 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. > > 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.
