Thread: [SQLObject] RelatedJoin to same table -- possible?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Peter H. <tph...@gm...> - 2005-08-18 19:09:39
|
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 |
From: Oleg B. <ph...@ma...> - 2005-08-19 05:40:55
|
On Wed, Aug 17, 2005 at 07:08:44PM -0400, Peter Herndon wrote: > 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? Not a bug, but deficiency. SQLObject has problems handling self-joins. You have to create tables by hands, and use sqlobject.sqlbuilder.Alias and related helpers to build SQL queries. Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Peter H. <tph...@gm...> - 2005-08-19 20:29:54
|
Hi Oleg, Thanks for the quick reply. On 8/19/05, Oleg Broytmann <ph...@ma...> wrote: > On Wed, Aug 17, 2005 at 07:08:44PM -0400, Peter Herndon wrote: > > 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? >=20 > Not a bug, but deficiency. SQLObject has problems handling self-joins. >=20 > You have to create tables by hands, and use sqlobject.sqlbuilder.Alias > and related helpers to build SQL queries. >=20 I think I found a way around it. I create two classes, Companies and Partners, as follows: class Companies(SQLObject): partners =3D MultipleJoin('Partners') partnerOf =3D MultipleJoin('Partners') class Partners(SQLObject): company_id1 =3D ForeignKey('Companies') company_id2 =3D ForeignKey('Companies') This way is somewhat painful to write, and I haven't actually used it in practice yet, but it compiles and runs, and looks like it will do what I need. Any thoughts? ---Peter |
From: Bryan M. <bmu...@gm...> - 2005-08-22 04:38:02
|
On 8/18/05, Oleg Broytmann <ph...@ma...> wrote: > On Wed, Aug 17, 2005 at 07:08:44PM -0400, Peter Herndon wrote: > > 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? >=20 > Not a bug, but deficiency. SQLObject has problems handling self-joins. >=20 > You have to create tables by hands, and use sqlobject.sqlbuilder.Alias > and related helpers to build SQL queries. It looks like this applies to a class that has a MultipleJoin on itself too, right? I have something like this: class Requirement( SQLObject ): number =3D StringCol() description =3D StringCol() parent_req =3D ForeignKey( 'Requirement' ) sub_reqs =3D MultipleJoin( 'Requirement' ) =20 And when I do something like this: req =3D Requirement( 1 ) for sub_req in req.sub_reqs: print sub_req.description I get: sqlite.DatabaseError: no such column: requirement_id Is this the same SQLObject deficiency, or am I doing something else wrong? Bryan |
From: Oleg B. <ph...@ma...> - 2005-08-22 05:10:43
|
On Sun, Aug 21, 2005 at 09:37:51PM -0700, Bryan Murdock wrote: > class Requirement( SQLObject ): > number = StringCol() > description = StringCol() > parent_req = ForeignKey( 'Requirement' ) > sub_reqs = MultipleJoin( 'Requirement' ) > > And when I do something like this: > > req = Requirement( 1 ) > for sub_req in req.sub_reqs: > print sub_req.description > > I get: > > sqlite.DatabaseError: no such column: requirement_id > Is this the same SQLObject deficiency, or am I doing something else wrong? That's another deficiency. It was discussed in the mailing list many times. Try sub_reqs = MultipleJoin( 'Requirement', joinColumn="parent_req_id") http://www.google.com/search?q=sqlobject+joincolumn Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |