sqlalchemy polymorphic many to many relation

Question

I'm trying to allow users to 'favorite' different items in my web app. So, for example, a user can favorite a comment and favorite a news story. I then want to query all of the items a user has favorited, and load the associated objects (whether it be a news story or comment etc) polymorphically to display the list of objects to the user. I know how to create a one to one polymorphic relationship, however I have not been able to figure out many to many.

Thanks in advance

EDIT

In my one-to-one polymorphic relationship I have a one-many relation between users and user actions, and a one-one polymorphic relationship between user actions and the object the action was performed on. So in this case my user action table is like so:

class UserAction:  
  pass

user_actions = Table('user_action', metadata,  
    Column('id', Integer, autoincrement=True, primary_key=True),
    Column('module', String(30)),
    Column('created', DateTime, default=datetime.now),  
    Column('user_id', Integer, ForeignKey('user.id'))  
)

news table (one of the objects that can be accessed via a user action):

class News:  
  pass

news = Table('news', metadata,  
    Column('id', Integer, autoincrement=True, primary_key=True),  
    Column('title', String(30), nullable=False),  
    Column('action_id', Integer, ForeignKey('user_action.id'))  
)

And the mappers:

mapper(UserAction, user_actions, polymorphic_on=user_actions.c.module, polymorphic_identity='user_action')  
mapper(News, news, inherits=UserAction, polymorphic_identity='news')

As you can see, there is a clear one-one relation between a news object and the user_action record associated with it. This is because the user_action foreign key is in the news table. If I wanted to go about creating a a many to many polymorphic relation where multiple different object types can be favorited by many users how would I go about doing this? Thanks so much.

Answer 1

Your example defines one-to-many relation between UserAction and News. It looks like mess to me since I see no reason why News inherits from UserAction. To allow several user actions refering single news you have to define intermediate table with two foreign keys: one refering to UserAction and other to News. I see two reasonable ways to make it polymorphic:

1. Use separate intermediate table for each favourited model class and define different relations in each UserAction subclasses.
2. Define separate foreign key for each favourited model in intermediate table and map it to class hierarchy with single-table inheritance (something like UserActionItem, UserActionNewsItem etc.).

But note, that all above is for linking UserAction and some entry models with many-to-many relation. While faouriting entries by users seems to me more like linking User with entry models.

Update: Below is working example. The only problem I see with it is that it allows duplicates.

from sqlalchemy import *
from sqlalchemy.orm import mapper, relation, sessionmaker
from sqlalchemy.ext.associationproxy import association_proxy

metadata = MetaData()

users = Table(
    'users', metadata,
    Column('id', Integer, nullable=False, primary_key=True),
)

news = Table(
    'news', metadata,
    Column('id', Integer, nullable=False, primary_key=True),
)

comments = Table(
    'comments', metadata,
    Column('id', Integer, nullable=False, primary_key=True),
)

favitems = Table(
    'favitems', metadata,
    Column('id', Integer, nullable=False, primary_key=True),
    Column('user_id', Integer, ForeignKey(users.c.id), nullable=False),
    Column('item_type', Integer, nullable=False),
    Column('news_id', Integer, ForeignKey(news.c.id)),
    Column('comment_id', Integer, ForeignKey(comments.c.id)),
)

class News(object): pass

class Comment(object): pass

class FavItem(object):
    TYPE_NEWS = 1
    TYPE_COMMENT = 2
    def __new__(cls, item=None):
        if isinstance(item, News):
            cls = FavNews
        elif isinstance(item, Comment):
            cls = FavComment
        return object.__new__(cls)

class FavNews(FavItem):
    def __init__(self, item):
        self.item_type = self.TYPE_NEWS
        self.item = item

class FavComment(FavItem):
    def __init__(self, item):
        self.item_type = self.TYPE_COMMENT
        self.item = item

class User(object):
    favorites = association_proxy('_favitems', 'item', creator=FavItem)

mapper(News, news)

mapper(Comment, comments)

mapper(FavItem, favitems,
       polymorphic_on=favitems.c.item_type)

mapper(FavNews, favitems,
       inherits=FavItem,
       polymorphic_identity=FavItem.TYPE_NEWS,
       properties={
            'item': relation(News),
       })

mapper(FavComment, favitems,
       inherits=FavItem,
       polymorphic_identity=FavItem.TYPE_COMMENT,
       properties={
            'item': relation(Comment),
       })

mapper(User, users,
       properties={
            '_favitems': relation(FavItem),
       })

engine = create_engine('sqlite://')
metadata.create_all(engine)
session = sessionmaker(engine)()

user = User()
news1 = News()
news2 = News()
comment1 = Comment()
comment2 = Comment()
user.favorites = [news1, news2, comment1, comment2]
session.add(user)
session.commit()
user_id = user.id

session.expunge_all()
user = session.query(User).get(user_id)
print user.favorites