Warning: Can't synchronize with repository "(default)" (Unsupported version control system "svn": No module named svn). Look in the Trac log for more information.

Changes between Initial Version and Version 1 of JoinaTableWithItself

06/24/06 16:07:15 (12 years ago)



  • JoinaTableWithItself

    v1 v1  
     1= Use Case = 
     2Sometimes 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. 
     4for example take you have a Map, each Map has several Regions and each Region knows it's borders you will do something like this. 
     7class Map(SQLObject): 
     8    name=StringCol() 
     9    regions=RelatedJoin("Region") 
     10class Region(SQLObject): 
     11    name =  StringCol(alternateID=True) 
     12    borders = RelatedJoin("Region") 
     14SQLObjects 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. 
     16= The Problem = 
     17The problem is that by default RelatedJoin does the following 
     19it takes class A and B, then creates a table call A_B that has this format 
     21CREATE TABLE A_B ( 
     22A_id INT NOT NULL, 
     23B_id INT NOT NULL 
     27now when A and B are the same object the table created is  
     29CREATE TABLE A_A ( 
     30A_id INT NOT NULL, 
     34so in our example you will end up with something like this 
     36CREATE TABLE region_region ( 
     37region_id INT NOT NULL, 
     40which is useless to regions to know who is adjacent to them. 
     41= The Fix =  
     43two parameters to the rescue  
     44joinColumn and otherColumn 
     46your Region class should look like this 
     48class Region(SQLObject): 
     49    name =  StringCol(alternateID=True) 
     50    borders = RelatedJoin("Region",joinColumn='some_id',otherColumn='other_id') 
     53that will generate 
     55CREATE TABLE region_region ( 
     56some_id INT NOT NULL, 
     57other_id INT NOT NULL 
     61which 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  
     64[http://groups.google.com/group/turbogears/browse_frm/thread/347462c74e37dd94/58e9ef4ae19183d3?q=+How+to+join+a+table+with+itself+in+the+model&rnum=1#58e9ef4ae19183d3 Mailing list related discussion ]