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.
