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

[Simon L. <sl...@re...>]

Hi, 

Thanks for the quick reply.  Unfortunately, I am still having problems getting it to work.  I'll answer your questions in order and then tell you what I've done and the error's I'm getting.

> First, why do you want this at all? Why not allow SQLObject to do internal referencing itself?...
> BTW, do you need many-to-many or one-to-many which is implemented by MultipleJoin

I need to make the intermediate table myself using the alternateID 'username' so as to be able to use the info from that table with fewer joins and to simplify some triggers.


What I've implemented:

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...

Am I missing something?

Thanks again for your assistance.
-Slaalo

-----Original Message-----
From: Oleg Broytman [mailto:ph...@ph...] 
Sent: Thursday, December 30, 2010 12:51 PM
To: sql...@li...
Subject: Re: [SQLObject] using alternateIDs as the joining id found in a Related Join's otherColumn

On Thu, Dec 30, 2010 at 12:26:50PM -0800, Simon Laalo wrote:
> I'm fairly new to SQLObject

   Welcome!

> I have a question about RelatedJoin: is it possible to use an alternateID as the data for the otherColumn.
> 
> I have something like the following
> 
> in the DB:
> users table with columns id ( int) and username (char 8)
> 
> roles table with columns id (int) and name (char 8)
> 
> user_roles table with columns username and role_id
> 
> in the model.py:
> class User(SQLObject):
>                 class sqlmeta:
>                                 table = 'users'
>                 username = UnicodeCol(alternateID=True, varchar=False, length=8,alternateMethodName='by_username')
> 
> 
> class Role(SQLObject):
>                 class sqlmeta:
>                                 table = 'roles'
>                 name = UnicodeCol(varchar=False, length=8)
> 
>                 users = RelatedJoin('User', intermediateTable='user_roles',
>                          joinColumn='role_id', otherColumn='username')
> 
> but this isn't working because when I try to get role.users it attempts to find users whose ID is their username.
> 
> Is there a way for this to work using the user's alternateID username in the join table?

   First, why do you want this at all? Why not allow SQLObject to do
internal referencing itself?
   Well, if you still want to do the work manually, well... it has to be
performed manually.
   RelatedJoin is many-to-many relation (BTW, do you need many-to-many
or one-to-many which is implemented by MultipleJoin) and works using an
intermediate table that stores references to both tables. By default the
intermediate table is created with INT columns to store references to
id's. If you want to store references to a UnicodeCol you have to create
the intermediate table yourself:
http://sqlobject.org/FAQ.html#how-can-i-define-my-own-intermediate-table-in-my-many-to-many-relationship
   And joinColumn has to be "username" in this case.

   I've never tried doing something like this, so I'm not sure it will
work.

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

------------------------------------------------------------------------------
Learn how Oracle Real Application Clusters (RAC) One Node allows customers
to consolidate database storage, standardize their database environment, and, 
should the need arise, upgrade to a full multi-node Oracle RAC database 
without downtime or disruption
http://p.sf.net/sfu/oracle-sfdevnl
_______________________________________________
sqlobject-discuss mailing list
sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss