[sqlalchemy] spanning relationship on 3 tables fails

[neurino]

I have this 4 tables, the base concept is:

`sensor data comes from its id_meas / id_cu pair, I can find id_meas
directly in data and id_cu in data parent acquisition.`

data = Table('data', metadata,
Column('id', Integer, primary_key=True),
Column('id_acq', Integer, ForeignKey('acquisitions.id'),
nullable=False),
Column('id_meas', Integer, nullable=False),
Column('value', Float, nullable=True),
)

acquisitions = Table('acquisitions', metadata,
Column('id', Integer, primary_key=True),
Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
nullable=False),
Column('datetime', DateTime, nullable=False),
)

sensors = Table('sensors', metadata,
Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
primary_key=True,
autoincrement=False),
Column('id_meas', Integer, primary_key=True, autoincrement=False),
Column('name', Unicode(20), nullable=False),
Column('desc', Unicode(40), nullable=False),
)

ctrl_units = Table('ctrl_units', metadata,
Column('id', Integer, primary_key=True, autoincrement=False),
Column('desc', Unicode(40), nullable=False)
)

and this mapping:

...
orm.mapper(Sensor, sensors,
properties={
'data': orm.relationship(Data, backref='sensor',
primaryjoin=and_(sensors.c.id_meas==data.c.id_meas,
data.c.id_acq==acquisitions.c.id,
acquisitions.c.id_cu==sensors.c.id_cu),
cascade='all, delete-orphan', single_parent=True)
})
...

and I can't get 'data' relationship to work, I get this error:

sqlalchemy.exc.ArgumentError: Could not locate any equated, locally
mapped column pairs for primaryjoin condition 'sensors.id_meas =
data.id_meas AND data.id_acq = acquisitions.id AND acquisitions.id_cu
= ctrl_units.id AND ctrl_units.id = sensors.id_cu' on relationship
Sensor.data. For more relaxed rules on join conditions, the
relationship may be marked as viewonly=True.

I also tried involving also ctr_units in primary join but had same
error.

Thanks for your support

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

Re: [sqlalchemy] spanning relationship on 3 tables fails

[Michael Bayer]

this is again my error messages not telling the whole story, ill see if i can 
get the term foreign_keys back in there:

mapper(Sensor, sensors,
   properties={
   'data': relationship(Data, backref='sensor',
foreign_keys=[data.c.id_meas],
   primaryjoin=and_(sensors.c.id_meas==data.c.id_meas,
   data.c.id_acq==acquisitions.c.id,
   acquisitions.c.id_cu==sensors.c.id_cu),
   cascade='all, delete-orphan', single_parent=True)
   })

or

mapper(Sensor, sensors,
   properties={
   'data': relationship(Data, backref='sensor',
foreign_keys=[sensors.id_meas],
   primaryjoin=and_(sensors.c.id_meas==data.c.id_meas,
   data.c.id_acq==acquisitions.c.id,
   acquisitions.c.id_cu==sensors.c.id_cu),
   cascade='all, delete-orphan', single_parent=True)
   })

depending on if this is one-to-many or many-to-one.A relationship like this 
is really better off as a viewonly=True since populating it is not going to add 
rows to the acquisitions table.

On Dec 30, 2010, at 10:15 AM, neurino wrote:

 data = Table('data', metadata,
Column('id', Integer, primary_key=True),
Column('id_acq', Integer, ForeignKey('acquisitions.id'),
 nullable=False),
Column('id_meas', Integer, nullable=False),
Column('value', Float, nullable=True),
)
 
 acquisitions = Table('acquisitions', metadata,
Column('id', Integer, primary_key=True),
Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
 nullable=False),
Column('datetime', DateTime, nullable=False),
)
 
 sensors = Table('sensors', metadata,
Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
 primary_key=True,
autoincrement=False),
Column('id_meas', Integer, primary_key=True, autoincrement=False),
Column('name', Unicode(20), nullable=False),
Column('desc', Unicode(40), nullable=False),
)
 
 ctrl_units = Table('ctrl_units', metadata,
Column('id', Integer, primary_key=True, autoincrement=False),
Column('desc', Unicode(40), nullable=False)
)
 
 and this mapping:
 
 ...
 orm.mapper(Sensor, sensors,
properties={
'data': orm.relationship(Data, backref='sensor',
primaryjoin=and_(sensors.c.id_meas==data.c.id_meas,
data.c.id_acq==acquisitions.c.id,
acquisitions.c.id_cu==sensors.c.id_cu),
cascade='all, delete-orphan', single_parent=True)
})
 ...

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