Hello list,
I'm having trouble trying to define a many-to-many relationship from a table
to itself (of course using an intermediate table).
I'll try to explain it with an example: say we have a table People, which
holds
all relevant information about people (for the sake of simpicity, in this
example we'll store just the name).
We want to implement a many-to-many relationship between people, Pseudonyms,
so a People can have several pseudonyms and a pseudonym can refer to several
people. Note that pseudonyms ae also People, which is the real interest of
the relation.
So, having into account that we *need to* have pseudonyms being People entries
(which in this example could be circumvented but in my real case can't) I end
up with this snippet of code:
------------------------------------------------------------------------------
#!/usr/bin/env python
from sqlalchemy import *
engine = create_engine('sqlite', {'filename':'testassoc.sqlite'}, echo=True)
people = Table('People', engine,
Column('ID', Integer, key='id', primary_key=True),
Column('Name', String(200), key='name', nullable=False),
)
pseudonyms = Table('Pseudonyms', engine,
Column('PeopleID', Integer, ForeignKey(people.c.id), key='peopleid'),
Column('PseudonymID', Integer, ForeignKey(people.c.id),
key='pseudonymid'),
)
class Entity(object):
def display(self, fnc):
c = [ "%s=%s" % (col.key, fnc(getattr(self, col.key))) for col in
self.c ]
return "%s(%s)" % (self.__class__.__name__, ' '.join(c))
def __repr__(self):
return self.display(repr)
def __str__(self):
return self.display(str)
class People(Entity):
pass
assign_mapper(People, people)
People.mapper.add_property('pseudonym_list', relation(
People.mapper, secondary=pseudonyms, lazy=True, uselist=True,
primaryjoin=people.c.id==pseudonyms.c.peopleid,
secondaryjoin=people.c.id==pseudonyms.c.pseudonymid,
foreignkey=people.c.id,
))
People.mapper.add_property('real_names', relation(
People.mapper, secondary=pseudonyms, lazy=True, uselist=True,
primaryjoin=people.c.id==pseudonyms.c.pseudonymid,
secondaryjoin=people.c.id==pseudonyms.c.peopleid,
foreignkey=people.c.id,
))
------------------------------------------------------------------------------
The two properties, pseudonym_list and real_names, give exceptions, in this
case ArgumentError("No syncrules generated for join criterion " +
str(sqlclause)).
I've tried all parameter combinations I've managed to read ino the docs (i.e.,
use/dont'use primaryjoin, secondaryjoin, foreignkey, etc.) but all I've got
are different exceptions.
I'm using the current SVN version (r1272). The relationships worked well once,
I think about r950, with the same configuration which appears in the example
but putting the secondary join clause wit a text instead of using table.c.col,
i.e., I used in 'pseudonym_list'
secondaryjoin=text("People.ID = Pseudonyms.PseudonymID"),
and a similar clause in 'real_names'. Currently these clauses doesn't work and
I suspect that they worked in the r950 revision by accident.
Could someone throw some light into the problem?
Thanks a lot,
Raul Garcia.
-------------------------------------------------------
This SF.Net email is sponsored by xPML, a groundbreaking scripting language
that extends applications into web and mobile media. Attend the live webcast
and join the prime developer group breaking into this new coding territory!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
