ManyToMany relationships on the same table in SQLObject

Question

My google foo has been coming up short on this, so I throw this to the geniuses here.

I'm writing some beer recipe creation software and I've got a class in SQLObject and I'd like to have a RelatedJoin back to itself. But it's not working.

If it matters I'm using SQLite3.

Here's the table:

class Hop(SQLObject):
    BITTERING = 0 
    AROMA = 1
    BOTH = 2
    LEAF = 0
    PELLET = 1
    PLUG = 2
    hop_types = ['Bittering', 'Aroma', 'Both',]
    hop_forms = ['Leaf', 'Pellet', 'Plug',]

    hop_type = IntCol(default=BITTERING)
    hop_form = IntCol(default=LEAF)
    alpha = PercentCol(default=0.0)
    beta = PercentCol(default=0.0)
    stability = PercentCol(default=0.0)
    origin = UnicodeCol(default=None)
    name = UnicodeCol(length=64, default=None)
    description = UnicodeCol(default=None)
    substitutes = RelatedJoin('Hop')

And here's the error:

>>> hop = Hop(name='Cascade', hop_form=LEAF, alpha=5.5, beta=4.8, stability=98.0, origin='USA', description='Tasty!')
>>> hop.id
2
>>> substitute_hop = Hop.get(1)
>>> hop.addHop(subtitute_hop)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "<string>", line 1, in <lambda>
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLObject-0.13.0-py2.7.egg/sqlobject/joins.py", line 230, in add
    getID(other))
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLObject-0.13.0-py2.7.egg/sqlobject/dbconnection.py", line 574, in _SO_intermediateInsert
    self.sqlrepr(secondValue)))
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLObject-0.13.0-py2.7.egg/sqlobject/dbconnection.py", line 349, in query
    return self._runWithConnection(self._query, s)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLObject-0.13.0-py2.7.egg/sqlobject/dbconnection.py", line 262, in _runWithConnection
    val = meth(conn, *args)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLObject-0.13.0-py2.7.egg/sqlobject/dbconnection.py", line 346, in _query
    self._executeRetry(conn, conn.cursor(), s)
  File "/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/SQLObject-0.13.0-py2.7.egg/sqlobject/sqlite/sqliteconnection.py", line 187, in _executeRetry
    raise OperationalError(ErrorMessage(e))
sqlobject.dberrors.OperationalError: no such table: hop_hop

Here is the function that generates the database

def connect_db(config):
    init = False
    if not os.path.exists(config['DB_NAME']):
        init = True
    connection = connectionForURI("%s%s%s" % (config['DB_DRIVER'],
                                              config['DB_PROTOCOL'],
                                              config['DB_NAME']))
    sqlhub.processConnection = connection
    if init:
        init_db(config)

def init_db(config):
    tables = [Entry, Users, Tag, Image, Hop, Grain, Extract, HoppedExtract,
              Yeast, Water, Misc, Mineral, Fining, Flavor, Spice, Herb,
              BJCPStyle, BJCPCategory,  MashTun, BoilKettle, EquipmentSet,
              MashProfile, MashStep, MashStepOrder, Recipe, RecipeIngredient,
              Inventory]
    for table in tables:
        try:
            table.createTable()
        except OperationalError:
            pass
    admin = Users(email=config['ADMIN_USERNAME'])
    admin.set_pass(config['PASSWORD_SALT'], config['ADMIN_PASSWORD'])
    admin.admin = True

Answer 1

Solved it. You have explicitly name the columns and the table for the join since it's only "one-sided":

class Hop(SQLObject):
    BITTERING = 0 
    AROMA = 1
    BOTH = 2
    LEAF = 0
    PELLET = 1
    PLUG = 2
    hop_types = ['Bittering', 'Aroma', 'Both',]
    hop_forms = ['Leaf', 'Pellet', 'Plug',]

    hop_type = IntCol(default=BITTERING)
    hop_form = IntCol(default=LEAF)
    alpha = PercentCol(default=0.0)
    beta = PercentCol(default=0.0)
    stability = PercentCol(default=0.0)
    origin = UnicodeCol(default=None)
    name = UnicodeCol(length=64, default=None)
    description = UnicodeCol(default=None)
    substitutes = RelatedJoin('Hop',
                              joinColumn='master_hop',
                              otherColumn='substitute_hop',
                              addRemoveName="Substitute",
                              intermediateTable="substitute_hops",
                              createRelatedTable=True)
    versions = Versioning()