On 08/09/2011 02:38 PM, Wichert Akkerman wrote:
I have a model where I have articles and images, with a many-to-many
relation between them. Since the order of images for an article is
important this relation should be ordered from the article site. My
naieve implementation looks like this:
article_images = Table('article_image', BaseObject.metadata,
Column('article_id', Integer(),
ForeignKey('article.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True),
Column('image_id', Integer(),
ForeignKey('image.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True),
Column('position', Integer()))
class Image(BaseObject):
__tablename__ = 'image'
id = Column(Integer(),
Sequence('image_id_seq', optional=True),
primary_key=True, autoincrement=True)
path = Column(String(128), nullable=False, unique=True)
class Article(BaseObject):
__tablename__ = 'article'
id = Column(Integer(),
Sequence('article_id_seq', optional=True),
primary_key=True, autoincrement=True)
#: An ordered list of images for this article. The first image
#: is considered to be the *key* image.
images = relationship(Image,
order_by=[article_images.c.position],
secondary=article_images,
collection_class=ordering_list('position'))
Unfortunately this breaks since OrderingList assumes that the position
attribute is set on Image instead of the article_images table. From
what I can see this is not easily fixed since OrderingList only gets
the list of Image instances and doesn't have access to the related
article_images row. Is there another way to accomplish this?
I found a possible workaround in an earlier post to this list:
http://groups.google.com/group/sqlalchemy/browse_thread/thread/611c88ee27354246
. The approach is interesting: it hides the many-to-many relationship
behind an extra association proxy, giving OrderingList something to work
with. I've copied a modified version of my test below to demonstrate how
this works with declarative syntax. Is this still the recommended
approach to do this?
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.orderinglist import ordering_list
from sqlalchemy.ext.associationproxy import association_proxy
engine = create_engine('sqlite:///')
metadata = MetaData(engine)
BaseObject = declarative_base(metadata=metadata)
class Image(BaseObject):
__tablename__ = 'image'
id = Column(Integer(),
Sequence('image_id_seq', optional=True),
primary_key=True, autoincrement=True)
path = Column(String(128), nullable=False, unique=True)
class ArticleImage(BaseObject):
__tablename__ = 'article_image'
article_id = Column(Integer(),
ForeignKey('article.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True)
image_id = Column(Integer(),
ForeignKey('image.id',
onupdate='CASCADE', ondelete='CASCADE'),
primary_key=True)
image = relationship(Image)
position = Column(Integer())
def __init__(self, image=None, **kw):
if image is not None:
kw['image'] = image
BaseObject.__init__(self, **kw)
class Article(BaseObject):
__tablename__ = 'article'
id = Column(Integer(),
Sequence('article_id_seq', optional=True),
primary_key=True, autoincrement=True)
_images = relationship(ArticleImage,
order_by=[ArticleImage.position],
collection_class=ordering_list('position'))
images = association_proxy('_images', 'image')
metadata.create_all()
session = create_session()
article = Article()
session.add(article)
article.images.append(Image(path='one'))
article.images.append(Image(path='two'))
article.images.append(Image(path='three'))
print session.query(ArticleImage).count()
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
