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 and 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 <rocambol...@gmail.com <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 <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 <rocambol...@gmail.com> 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 <rocambol...@gmail.com> 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 <rocambol...@gmail.com> 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 sqlalchemy+...@googlegroups.com. > > > > To post to this group, send email to sqlal...@googlegroups.com. > > > > 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 sqlalchemy+...@googlegroups.com. > > > To post to this group, send email to sqlal...@googlegroups.com. > > > 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 sqlalchemy+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <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 sqlalchemy+unsubscr...@googlegroups.com > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > 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 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/d/optout.