SQLAlchemy, self-referential secondary table association

Question

I'm trying to create a "Product" object in SQLAlchemy and so far I have gotten everything working accept the Product's "accessories". What I have is a Product with a list of field/value pairs (ie. Capacity : 12 L etc.), and integer ID and a catalog number. I would like to be able to associate certain "accessories" with a given product where these accessories are also products. Essentially I just want an association table that has product_id and accessory_id which both reference the ID in the Products table. This is what I have so far:

product_accessories=Table('product_accesssories',Base.metadata,\
                   Column('product_id',Integer,ForeignKey('product.product_id')),\
                   Column('accessory_id',Integer,ForeignKey('product.product_id')))

class Product(Base):
    __tablename__='product'
    id=Column('product_id',Integer,primary_key=True)
    catNo=Column('catalog_number',String(20))
    fields=relationship('ProductField')
    accessories=relationship('Product',secondary=product_accessories)

I get the error "Could not determine join condition between parent/child tables on relationship Product.accessories". I have tried fiddling around a bunch with this and haven't been able to get anywhere. I don't want to lose the unique reference to a product and I feel like there should be an easier way to do this other than wrapping accessories in another class.

Any help would be greatly appreciated!

Answer 1

import sqlalchemy as sa
from sqlalchemy import orm

products_table = sa.Table('products', metadata,
    sa.Column('id', sa.Integer(), primary_key=True),
    sa.Column('catalog_number', sa.String(20)),
)

accessories_table = sa.Table('product_accessories', metadata,
    sa.Column('product_id', sa.ForeignKey(products_table.c.id), primary_key=True),
    sa.Column('accessory_id', sa.ForeignKey(products_table.c.id), primary_key=True),
)

class Product(object):
    pass

class Accessory(object):
    pass

orm.mapper(Product, products_table, properties=dict(
    accessories=orm.relation(Accessory,
        primaryjoin=(products_table.c.id == accessories_table.c.product_id),
        secondaryjoin=(products_table.c.id == accessories_table.c.accessory_id),
    ),
))
orm.mapper(Accessory, accessories_table)