Noel James wrote:
> Hello,
>
> I have a question about using multiple polymorphic tables with different
> parents which relate to the the same parent table but other polymorphic
> child.
>
> I have two tables staff (class Staff) and contract (class Contract).
> The Staff table has an identity manager (class Manager) and the contract
> table has an identity peon (class Peon).
> I have a third table called payments. Payments has two identities
> identity StatusReport and BillableHours.
>
> I want to have StausReport have a relationship to Peon
> ForeignKey('contract.id') and the BillableHours have a relationship to
> Manager ForeignKey('staff.id')
since you are using single table inheritance, you must have distinct
attribute names per class hierarchy. Here you have placed a "user" column
twice, both on StatusReport and then on BillableHours - these conflict and
apparently declarative is not smart enough to see this particular
condition ahead of time (I've added ticket #1732 with a new patch that
just needs tests for this feature). One column will need to be renamed
unless you'd like to break those into separate tables.
Additionally, you likely don't want two relations() on the
one-to-many/many-to-one side that aren't aware of each other - you'd want
to specify "back_populates='Peons'" on the Peon.StatusReports relation and
"back_populates='StatusReports" on the StatusReport.Peons relation.
>
> The relationships look like this:
> staff.id => payments.user_id
> contract.id => payments.user_id
> payments.user_id => staff.id
> payments.user_id => contract.id
>
> I am using SQLAlchemy v0.5.8 on Python v2.6
>
> Here is an *example* of one of the tests i have tried. I hope it gives
> a better idea of what I am tring to do (and is not just confusing).
> I get failures about specifying "foreign_keys" or "Could not locate any
> equated locally mapped column pairs..."
> --------------------------------------------------------------
>
> from sqlalchemy import MetaData, orm, schema
> from sqlalchemy.types import Integer, String
> from sqlalchemy.schema import Column, Sequence, ForeignKey
> import sqlalchemy as sa
> from sqlalchemy.orm import sessionmaker, relation
> from sqlalchemy.ext.declarative import declarative_base
>
> engine = sa.create_engine( 'sqlite://' )
> metadata = MetaData( )
> Base = declarative_base( metadata = metadata )
> Session = sessionmaker( )
> Session.configure( bind = engine )
>
> def create( ):
> Base.metadata.create_all( engine )
>
> class Staff( Base ):
> __tablename__ = 'staff'
> id = Column( Integer, Sequence( 'user_seq' ),
> nullable=False, primary_key=True )
> name = Column( String, nullable=False )
> type = Column( String, nullable=False )
> __mapper_args__ = {'polymorphic_on': type }
>
> class Manager( Staff ):
> __mapper_args__ = {'polymorphic_identity': 'manager'}
> Billables = relation( 'BillableHours' )
>
>
> class Contract( Base ):
> __tablename__ = 'contract'
> id = Column( Integer, Sequence( 'contract_seq' ),
> nullable=False, primary_key=True )
> type = Column( String, nullable=False )
> __mapper_args__ = {'polymorphic_on': type }
>
> class Peon( Contract ):
> __mapper_args__ = {'polymorphic_identity': 'peon'}
> StatusReports = relation( 'StatusReport' )
>
>
> class Payments( Base ):
> __tablename__ = 'payments'
> id = Column( Integer, Sequence( 'payments_seq' ),
> nullable=False, primary_key=True )
> type = Column( String, nullable=False )
> __mapper_args__ = {'polymorphic_on': type }
>
> class StatusReport( Payments ):
> __mapper_args__ = {'polymorphic_identity': 'status'}
> user = Column( Integer, ForeignKey('contract.id'), nullable=False )
> job = Column( String, nullable=False, default="offshore" )
> hrs = Column( Integer, nullable=False, default=0 )
> Peons = relation( 'Peon' )
>
> class BillableHours( Payments ):
> __mapper_args__ = {'polymorphic_identity': 'billable'}
> user = Column( Integer, ForeignKey('staff.id'), nullable=False )
> job = Column( String, nullable=False )
> hrs = Column( Integer, nullable=False, default=8 )
> Managers = relation( 'Manager' )
> --------------------------------------------------------------
>
> Thanks.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
