[sqlalchemy] How to replace a property in a declarative way

[Kees van den Broek]

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 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
-~----------~----~----~----~------~----~------~--~---