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 Version 2 and Version 3 of SQLObjectTreeLike


Ignore:
Timestamp:
06/24/06 15:54:29 (11 years ago)
Author:
jorge.vargas
Comment:

--

Legend:

Unmodified
Added
Removed
Modified
  • SQLObjectTreeLike

    v2 v3  
    1 = Use Case = 
    2 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. 
     1you may want to take a look at joinATableWithItself 
    32 
    4 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. 
     3or someone posted a clever way to store paths in  
    54 
    6 {{{ 
    7 class Map(SQLObject): 
    8     name=StringCol() 
    9     regions=RelatedJoin("Region") 
    10 class Region(SQLObject): 
    11     name =  StringCol(alternateID=True) 
    12     borders = RelatedJoin("Region") 
    13 }}} 
    14 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. 
    15  
    16 = The Problem = 
    17 The problem is that by default RelatedJoin does the following 
    18  
    19 it takes class A and B, then creates a table call A_B that has this format 
    20 {{{ 
    21 CREATE TABLE A_B ( 
    22 A_id INT NOT NULL, 
    23 B_id INT NOT NULL 
    24 ); 
    25 }}} 
    26  
    27 now when A and B are the same object the table created is  
    28 {{{ 
    29 CREATE TABLE A_A ( 
    30 A_id INT NOT NULL, 
    31 ); 
    32 }}} 
    33  
    34 so in our example you will end up with something like this 
    35 {{{ 
    36 CREATE TABLE region_region ( 
    37 region_id INT NOT NULL, 
    38 ); 
    39 }}} 
    40 which is useless to regions to know who is adjacent to them. 
    41 = The Fix =  
    42  
    43 two parameters to the rescue  
    44 joinColumn and otherColumn 
    45  
    46 your Region class should look like this 
    47 {{{ 
    48 class Region(SQLObject): 
    49     name =  StringCol(alternateID=True) 
    50     borders = RelatedJoin("Region",joinColumn='some_id',otherColumn='other_id') 
    51 }}} 
    52  
    53 that will generate 
    54 {{{ 
    55 CREATE TABLE region_region ( 
    56 some_id INT NOT NULL, 
    57 other_id INT NOT NULL 
    58 ); 
    59 }}} 
    60  
    61 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  
    62  
    63 ---- 
    645[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 ]