Re: [SQLObject] using alternateIDs as the joining id found in a Related Join's otherColumn

[Oleg B. <ph...@ph...>]

@All: Happy New Year, folks!

On Thu, Dec 30, 2010 at 05:50:30PM -0800, Simon Laalo wrote:
> class User(SQLObject):
> 	class sqlmeta:
>       	table = 'users'
> 	username = UnicodeCol(alternateID=True, varchar=False, length=8,alternateMethodName='by_username')
> 	roles = SQLRelatedJoin('Role', joinColumn='username', intermediateTable='user_roles', createRelatedTable=False, orderBy='ordinal')
> 
> 
> class Role(SQLObject):
> 	class sqlmeta:
>       	table = 'roles'
> 	name = UnicodeCol(varchar=False, length=8)
> 
> 	users = SQLRelatedJoin('User', intermediateTable='user_roles', createRelatedTable=False, otherColumn='username')
> 
> class UserRoles(SQLObject):
>     class sqlmeta:
>         table = 'user_roles'
>     
>     username = ForeignKey('User',notNull=True, cascade=True)
>     role = ForeignKey('Role', notNull=True, cascade=True)
>     ordinal = IntCol(notNone=True)
>     unique = index.DatabaseIndex(username, role, unique=True)
> 
> and when I call role.users I get the following error:
> 
> ProgrammingError: operator does not exist: integer = character LINE 1: ...user_roles, roles WHERE ((users.id = user_roles.username...

   Aha, I see problems with this approach. First, ForeignKey is
implemented using an INT column referencing id column of the
corresponding table, so ForeignKey cannot be used in the intermediate
table. It has to be replaced with a UnicodeCol, and you loose `cascade`.
   Anyway, the following program works for me:

class User(SQLObject):
    class sqlmeta:
        table = 'users'

    username = UnicodeCol(alternateID=True, varchar=False, length=8, alternateMethodName='by_username')
    roles = SQLRelatedJoin('Role', intermediateTable='user_roles', createRelatedTable=False)

class Role(SQLObject):
    class sqlmeta:
        table = 'roles'

    name = UnicodeCol(varchar=False, length=8)
    users = SQLRelatedJoin('User', intermediateTable='user_roles', createRelatedTable=False)

class UserRoles(SQLObject):
    class sqlmeta:
        table = 'user_roles'

    username = UnicodeCol(dbName='users_id', notNull=True, varchar=False, length=8)
    role = ForeignKey('Role', dbName='roles_id', notNull=True, cascade=True)
    unique = index.DatabaseIndex(username, role, unique=True)

User.createTable()
Role.createTable()
UserRoles.createTable()

user = User(username='test')
role = Role(name='test')
user.addRole(role)
#role.addUser(user)

print user.roles
print list(user.roles)

print role.users
print list(role.users)

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.