hi there
I would like to define a m:n relation between two tables that are linked by an
association table.
I am using MySQL 5.1 and SA 0.6.3
this is the select that I want to implement:
"""
select * from
cisdata c,
cisbr cb
branchen b,
where c.ID_cis = cb.ID_cisbr and cb.ID_br = b.ID_br and b.br =
'Fotografie'
"""
as you can see whe have two tables:
cis
branchen
that are linked trough an association table cisbr.
I am using the following setup:
Base = declarative_base(engine)
Base.metadata.reflect()
tables = Base.metadata.tables
# -----------------------------------------------------
# -----------------------------------------------------
#
class Cisdata(Base):
__table__ = tables['cisdata']
class Branchen(Base):
branche = relation(
'Cisdata',
secondary = tables['cisbr'],
primaryjoin = tables['cisdata'].c.ID_cis==tables['cisbr'].c.ID_cisbr,
secondaryjoin = tables['branchen'].c.ID_br==tables['cisbr'].c.ID_br,
foreign_keys = [tables['cisdata'].c.ID_cis,
tables['cisbr'].c.ID_cisbr, tables['cisbr'].c.ID_br, tables['branchen'].c.ID_br],
backref="firmen",
)
__table__ = tables['branchen']
now when I access a table
i get an error:
Could not determine relationship direction for primaryjoin condition
'cisdata.`ID_cis` = cisbr.`ID_cisbr`', on relationship Branchen.branche. Do the
columns in 'foreign_keys' represent only the 'foreign' columns in this join
condition ?
when I use the following setup:
I would be very glad, if somebody could tell me, what I am doing wrong.
thanks
robert
the association table cisbr has more fields than only the keys.
like this:
CREATE TABLE `cis`.`cisbr` (
`ID_cisbr` int(11) NOT NULL AUTO_INCREMENT,
`ID_br` int(11) NOT NULL,
`ID_cis` int(11) NOT NULL,
`value` smallint(6) NOT NULL DEFAULT '0',
`timestmp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (`ID_cisbr`),
KEY `ID_br` (`ID_br`,`ID_cis`,`value`)
)
--
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.
