[SQLObject] RelatedJoin to same table -- possible?

[Peter H. <tph...@gm...>]

Hi there,

I'm attempting to set up a subclass of SQLObject that has an attribute
that is a RelatedJoin on itself.  A typical use case would be, I have
a table of companies, some of whom are partners to others.  On an ERD,
I would represent this relationship as a many-to-many relationship of
the Companies table to itself.  In SQL (for SQLite, my target
backend), I'd write something like:

CREATE TABLE companies (company_id INTEGER PRIMARY KEY, ...);
CREATE TABLE partners (partner_id INTEGER PRIMARY KEY, company_id1
INTEGER, company_id2 INTEGER);

where company_id1 and company_id2 are foreign keys back to companies.=20
The problem I'm running into is that when I define my class, I wind up
with something like this:

class Companies(SQLObject):
    partners =3D RelatedJoin('Companies')

and when I create the table, I get an error in SQL syntax.  Turning on
debugging, I find the SQL emitted for Companies is fine, but the SQL
for Partners is b0rked:

CREATE TABLE partners (partner_id INTEGER PRIMARY KEY, company_id
INTEGER, company_id INTEGER);

Both foreign key columns are named the same.  Is this a bug in
SQLObject?  Is there a better way to accomplish my goal?

Thanks in advance,

---Peter