Pierre B <rocambolesque...@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+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.
--
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.
