Use Case

Sometimes you need something like a Page having child Pages or a Region having other Regions as borders or simply in SQL terms a Many-to-Many relation between the same table.

for example take you have a Map, each Map has several Regions and each Region knows it's borders you will do something like this.

class Map(SQLObject):
    name=StringCol()
    regions=RelatedJoin("Region")
class Region(SQLObject):
    name =  StringCol(alternateID=True)
    borders = RelatedJoin("Region")

SQLObjects RelatedJoin? handles Many-to-Many very well but it has a problem when the table is the same. Beware It will fail without errors! and you will think everything is ok.

The Problem

The problem is that by default RelatedJoin? does the following

it takes class A and B, then creates a table call A_B that has this format

CREATE TABLE A_B (
A_id INT NOT NULL,
B_id INT NOT NULL
);

now when A and B are the same object the table created is

CREATE TABLE A_A (
A_id INT NOT NULL,
);

so in our example you will end up with something like this

CREATE TABLE region_region (
region_id INT NOT NULL,
);

which is useless to regions to know who is adjacent to them.

The Fix

two parameters to the rescue joinColumn and otherColumn

your Region class should look like this

class Region(SQLObject):
    name =  StringCol(alternateID=True)
    borders = RelatedJoin("Region",joinColumn='some_id',otherColumn='other_id')

that will generate

CREATE TABLE region_region (
some_id INT NOT NULL,
other_id INT NOT NULL
);

which names should you give to join/other Column, whatever you like chances are you will never use this unless you need to do something with sqlbuilder

---

 Mailing list related discussion