Re: [SQLObject] Many-to-Many relationship - custom join table?

[Nick M. <nmu...@lo...>]

Hi Rick,

This does seem to work, indeed! Thanks very much!

For the record, the manual way mentioned by Oleg seemed to do the trick 
too (and I was half-way through writing up the process for future 
reference when I saw your suggestion), and I didn't get the chance to 
try Julio's suggestion.

Thanks again,

Nick


Rick Flosi wrote:
> Nick,
> 
> Try this:
> 
> class Task(SQLObject):
>      """
>      CHANGES:
>          * RelatedJoin --> SQLRelatedJoin
>          * added 'createRelatedTable=False' to SQLRelatedJoin
>      """
>      name = UnicodeCol()
>      users = SQLRelatedJoin('User',
>          joinColumn='user',
>          otherColumn='task',
>          intermediateTable='user_tasks',
>          createRelatedTable=False)
> 
> class User(SQLObject):
>      """
>      CHANGES:
>          * RelatedJoin --> SQLRelatedJoin
>          * added 'createRelatedTable=False' to SQLRelatedJoin
>      """
>      ...
>      tasks = SQLRelatedJoin('Task',
>          joinColumn='task',
>          otherColumn='user',
>          intermediateTable='user_tasks',
>          createRelatedTable=False)
> 
> class UserTasks(SQLObject):
>      """
>      CHANGES:
>          * added 'unique' index.
>            This will restrict the database from having 2 records
>            with user_id=1 and task_id=1 for example.
>            Which is what I think you want.
>      """
>      user = ForeignKey('User')
>      task = ForeignKey('Task')
>      priority = FloatCol()
>      unique = index.DatabaseIndex(user, task, unique=True)
> 
> --
> Rick
> 
> 
> On Thu, 16 Nov 2006, Nick Murdoch wrote:
> 
>> Hi,
>>
>> 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 model.py looks something like this:
>>
>> class Task(SQLObject):
>>     name = UnicodeCol()
>>     users = RelatedJoin('User', joinColumn='user', otherColumn='task',
>> intermediateTable='user_tasks')
>>
>> class User(SQLObject):
>>     ...
>>     tasks = RelatedJoin('Task', joinColumn='task', otherColumn='user',
>> intermediateTable='user_tasks')
>>
>>
>> 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 (
>> user INT NOT NULL,
>> task INT NOT NULL
>> );
>>
>>
>> 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!
>>
>> Thanks,
>>
>> Nick Murdoch
>>
>> -------------------------------------------------------------------------
>> Take Surveys. Earn Cash. Influence the Future of IT
>> Join SourceForge.net's Techsay panel and you'll get the chance to share your
>> opinions on IT & business topics through brief surveys - and earn cash
>> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
>> _______________________________________________
>> sqlobject-discuss mailing list
>> sql...@li...
>> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>>
> 
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss