Inherited class column override

[Pierre B]

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'

[Michael Bayer]

> 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).

[Pierre B]

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.

[Pierre B]

I made a type in the Right model, here are the models again:

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)

[Michael Bayer]

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.

> --
> 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.

[Pierre B]

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.

[Michael Bayer]

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

[Pierre B]

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'))

[Michael Bayer]

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.

[Pierre B]

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.

[Mike Bayer]

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.   

[Pierre B]

Here's a simple visual of the schema

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.

[Mike Bayer]

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.

[Pierre B]

Seems like exactly what I need! Many thanks for all your help, I will try that out right away :)

...