Hi,
I simplified my problem to a small example (see model below).
A Product has several Tags attached. A beachball is green, blue, round
and big:
p=Product()
p.name = "Beachball"
p.tags = [greenTag, blueTag, roundTag, bigTag]
DBSession.save(p)
Often I just want to see the color-related tags, not the other tags.
Therefore, I group all color tags in a Collection:
c=Collection()
c.name = "Colors"
c.tags = [greenTag, blueTag, brownTag]
DBSession.save(c)
See what colors are attached to the product:
>>> p = DBSession.query(Product).filter(Product.name == "Beachball").first()
>>> p.colors
[<Tag: id=1706, name=green>, <Tag: id=1707, name=blue>]
Here's my issue. To get the colors, I defined a property, which
results in an extra query.
I want the color Tags to be fetched in the same query when I fetch the
Product itself (optionally with lazy loading)
I guess I need another relation() definition with a Collection join
and Collection name filter applied.
I couldn't come up with the code to get me there. How should I tackle
this?
Cheers,
Kees
The model:
product_tag_table = Table('product_tag', metadata,
Column('product_id', Integer, ForeignKey('product.id',
onupdate="CASCADE", ondelete="CASCADE")),
Column('tag_id', Integer, ForeignKey('tag.id',
onupdate="CASCADE", ondelete="CASCADE"))
)
tag_collection_table = Table('tag_collection', metadata,
Column('tag_id', Integer, ForeignKey('tag.id',
onupdate="CASCADE", ondelete="CASCADE")),
Column('collection_id', Integer, ForeignKey('collection.id',
onupdate="CASCADE", ondelete="CASCADE"))
)
class Product(DeclarativeBase):
__tablename__ = 'product'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), nullable=False)
tags = relation('Tag', secondary=product_tag_table,
backref='products')
# XXX: REPLACE THIS PROPERTY WITH A FILTERED RELATION?
def _get_colors(self):
color_tags=DBSession.query(Collection).\
filter(Collection.name=="Colors").first().tags
return filter(lambda tag: tag in self.tags, color_tags)
colors = property(_get_colors)
class Tag(DeclarativeBase):
__tablename__ = 'tag'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
class Collection(DeclarativeBase):
__tablename__ = 'collection'
id = Column(Integer, autoincrement=True, primary_key=True)
name = Column(Unicode(255), unique=True, nullable=False)
tags = relation('Tag', secondary=tag_collection_table,
backref='collections')
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
