sorry for the forward mail.....

---------- Forwarded message ----------
From: Julio Oņa <>
Date: Nov 16, 2006 11:10 AM
Subject: Re: [SQLObject] Many-to-Many relationship - custom join table?
To: Nick Murdoch <>

A good alternative is:

class Task(SQLObject):
     name = UnicodeCol()

     def _get_users(self):
         return [ut.task for ut in UserTasks.selectBy(user=self)]

     def _set_users(self, users):
             lut = UserTasks.selectBy(task=self)
             usersT = [ut.user for ut in lut]
             for ut in lut:
                 if not ut.user in users:
                     ut.destroySelf ()
             for u in users:
                 if not u in usersT:
                     UserTasks(task=self, user=u)

class User(SQLObject):
     def _get_tasks(self):
         return [ut.user for ut in UserTasks.selectBy(task=self)]

     def _set_tasks(self, tasks):

class UserTasks(SQLObject):
     user = ForeignKey('User')
     task = ForeignKey('Task')
     priority = FloatCol()

yes I know is harder and duplicating code, but when you need it you need it.

hope it helps.


On 11/16/06, Nick Murdoch <> wrote:

I'm currently working on a TurboGears project that in its model has the
standard TurboGears User table, and a table of my own, Task. The idea is
that each user has a task list, and a task can have multiple Users
associated with it.

At the moment my looks something like this:

class Task(SQLObject):
     name = UnicodeCol()
     users = RelatedJoin('User', joinColumn='user', otherColumn='task',

class User(SQLObject):
     tasks = RelatedJoin('Task', joinColumn='task', otherColumn='user',

Now, I want to be able to assign each Task a priority, but I want two
Users to be able to prioritise a Task differently. To me, this would
mean putting an extra column in the user_tasks table to hold the
priority for each user/task pair, so I wrote:

class UserTasks(SQLObject):
     user = ForeignKey('User')
     task = ForeignKey('Task')
     priority = FloatCol()

I then ran 'tg-admin sql create' (which I presume calls some SQLObject
command internally), and it didn't throw up any errors, but when I
inspected the sqlite database, the extra priority field wasn't included!

sqlite> .tables
group_permission  tg_group          user_tasks
permission        tg_user           visit
task              user_group        visit_identity

sqlite> .schema user_tasks
CREATE TABLE user_tasks (

Presumably SQLObject creates the custom joining table first, then it
gets overwritten with the new auto-generated version?

Any help here would be great; I really need that priority column for
this project!


Nick Murdoch

Take Surveys. Earn Cash. Influence the Future of IT
Join's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
sqlobject-discuss mailing list