On Sep 22, 5:57 am, Kees van den Broek <[email protected]> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
