Using ordering_list with many to many relationships
Gareth
users, movies and user_movies as an ordered list - The ordering_list
extension looks to do what I want already, but I don't see how you'd
connect it to a many to many relationship and actually have it work.
Does anyone have any ideas, or failing that an alternate strategy for
managing the above?
Thanks
Gareth.
jason kirtland
Here's one way:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy.ext.associationproxy import association_proxy
meta = MetaData('sqlite://')
users = Table('users', meta,
Column('id', Integer, primary_key=True),
Column('name', String(128)))
movies = Table('movies', meta,
Column('id', Integer, primary_key=True),
Column('name', String(128)))
user_movies = Table('user_movies', meta,
Column('user_id', Integer,
ForeignKey('users.id'),
primary_key=True),
Column('movies_id', Integer,
ForeignKey('movies.id'),
primary_key=True),
Column('position', Integer))
meta.create_all()
class User(object):
movies = association_proxy('usermovies', 'movie')
class Movie(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return '<Movie %r>' % self.name
class UserMovie(object):
def __init__(self, movie):
self.movie = movie
mapper(User, users, properties={
'usermovies': relation(
UserMovie, backref='user',
cascade='all, delete-orphan',
collection_class=ordering_list('position'))})
mapper(UserMovie, user_movies, properties={
'movie': relation(Movie)})
mapper(Movie, movies)
session = create_session()
u = User()
u.movies.extend([Movie('a'), Movie('b'), Movie('c')])
session.save(u)
session.flush()
print list(user_movies.select().execute())
del u.movies[2]
session.flush()
print list(user_movies.select().execute())
