Hi all, I am trying to get my head around using a ManyToMany relation between tables and hope to get some guidance here since the available docs are a bit sparse on this (no offence - I know how difficult it is to keep docs up to date :-) )
Here is what I have: class Member(SQLObject): name = StringCol(alternateID=True) roles = ManyToMany('Role') class Role(SQLObject): name = StringCol(alternateID=True) members = ManyToMany('Member') I am testing with a SQLite backend for what it's worth. Q1: Is there a way, by adding additional params to the ManyToMany(...) declarations for example, to restrict the same role to be added multiple times to the same member and vice versa? Q2: Is there a way to restrict/cascade deletes of a role or member if that role or member has a link to the other? Q3: When deleting a role or member using the .destroySelf() method, the link record in the intermediate table is not deleted. Is there a standard way to do this? I suppose an answer to Q2 will also address this. I am adding a sample python file, and the output from an iPython session, below to illustrate the questions if that would help. Thanks, Tom ------------- cut : ManyToMany.py ----------------------------------- import os from sqlobject import * myDir = os.path.realpath(os.path.dirname(__file__)) dbURI = 'sqlite://%s/ManyToMany.sqlite' % myDir sqlhub.processConnection = connectionForURI(dbURI) class Member(SQLObject): name = StringCol(alternateID=True) roles = ManyToMany('Role') class Role(SQLObject): name = StringCol(alternateID=True) members = ManyToMany('Member') def initDB(): tables = (Member, Role) for t in tables: t.createTable(ifNotExists=True) def addSamples(): members = ('John', 'Paul', 'George', 'Ringo') roles = ('drums', 'lead', 'rythm', 'bass', 'vocals') for m in members: Member(name=m) for r in roles: Role(name=r) Member.byName('John').roles.add(Role.byName('rythm')) Member.byName('Paul').roles.add(Role.byName('bass')) Member.byName('George').roles.add(Role.byName('lead')) Member.byName('Ringo').roles.add(Role.byName('drums')) ------------- cut : ManyToMany.py ----------------------------------- ------------- cut : iPython session --------------------------------- In [1]: import ManyToMany as mm In [2]: mm.initDB() In [3]: mm.addSamples() In [4]: vocals = mm.Role.byName('vocals') In [5]: john = mm.Member.byName('John') In [6]: john.roles.add(vocals) -----> Q1: Added the 'vocals' role to john, and here it is: In [7]: list(john.roles) Out[7]: [<Role 3 name='rythm'>, <Role 5 name='vocals'>] In [8]: john.roles.add(vocals) -----> Q1: ... but we can add it a second time and now have the same role twice. Some way to specify a unique index on the intermediate table could avoid this and would raise a trapable exception. In [9]: list(john.roles) Out[9]: [<Role 3 name='rythm'>, <Role 5 name='vocals'>, <Role 5 name='vocals'>] ------> Q1: Here is the corresponding rows from the intermediate table for the 'vocals' role In [10]: Q = "SELECT * from member_role where role_id=5" In [11]: mm.Role._connection.queryAll(Q) Out[11]: [(1, 5), (1, 5)] ------> Q3: Destroy the 'vocals' role In [12]: vocals.destroySelf() ------> Q3: It's gone from john's roles In [13]: list(john.roles) Out[13]: [<Role 3 name='rythm'>] ------> Q3: ... but both records are still in the intermediate table In [14]: mm.Role._connection.queryAll(Q) Out[14]: [(1, 5), (1, 5)] ------> Here we add a new role 'foo'. It may just be an SQLite related 'feature', but this new role will get the same id as the just deleted 'vocals' role had. In [15]: foo = mm.Role(name='foo') In [16]: foo Out[16]: <Role 5 name='foo'> ------> ... and due to this, john will automatically get 2 'foo' roles added to his list of roles - not good :-( In [17]: list(john.roles) Out[17]: [<Role 3 name='rythm'>, <Role 5 name='foo'>, <Role 5 name='foo'>] ------------- cut : iPython session --------------------------------- ------------------------------------------------------------------------------ Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july _______________________________________________ sqlobject-discuss mailing list sqlobject-discuss@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss