Here's a simple visual of the schema
<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 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> 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 <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
>>
>> >
>> > 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 <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).
>>
>>
>> > > >
>> > > > 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 sqlalchemy+...@googlegroups.com.
>> > > > To post to this group, send email to sqlal...@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+...@googlegroups.com.
>> > > To post to this group, send email to sqlal...@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+...@googlegroups.com.
>> > To post to this group, send email to sqlal...@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+...@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.
> 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.
