On Sep 22, 9:39 am, Conor <[email protected]> wrote:
> 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
Sorry for the double post.
Here is how I would construct the "colors" relation (put this inside
the Product declaration):
def _secondaryjoin():
tag = Tag.__table__
collection = Collection.__table__
exists_clause = exists(
[1],
((tag_collection_table.c.tag_id == tag.c.id)
& (collection.c.name == "Colors")),
from_obj=tag_collection_table.join(collection)).correlate(tag)
# >>> print exists_clause
# EXISTS (SELECT 1
# FROM tag_collection JOIN collection ON collection.id =
tag_collection.collection_id
# WHERE tag_collection.tag_id = tag.id AND collection.name
= :name_1)
retval = ((tag.c.id == product_tag_table.c.tag_id) &
exists_clause)
# >>> print retval
# tag.id = product_tag.tag_id AND (EXISTS (SELECT 1
# FROM tag_collection JOIN collection ON collection.id =
tag_collection.collection_id
# WHERE tag_collection.tag_id = tag.id AND collection.name
= :name_1))
return retval
colors = relation('Tag',
secondary=product_tag_table,
secondaryjoin=_secondaryjoin,
viewonly=True)
del _secondaryjoin
The key here is adding a custom secondaryjoin to the relation to add
your own filters. The viewonly bit keeps you from trying to modify the
relation, because you should modify the "tags" relation instead. This
may not be the fastest clause to use as your secondaryjoin, but it was
the most straightforward one I could come up with.
As for eagerloading the "colors" relation, you can do:
q = session.query(Product)
q = q.options(eagerload("colors"))
# >>> print q
# SELECT product.id AS product_id, product.name AS product_name,
tag_1.id AS tag_1_id, tag_1.name AS tag_1_name
# FROM product LEFT OUTER JOIN product_tag AS product_tag_1 ON
product.id = product_tag_1.product_id LEFT OUTER JOIN tag AS tag_1 ON
tag_1.id = product_tag_1.tag_id AND (EXISTS (SELECT 1
# FROM tag_collection JOIN collection ON collection.id =
tag_collection.collection_id
# WHERE tag_collection.tag_id = tag_1.id AND collection.name
= :name_1))
-Conor
> > 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
-~----------~----~----~----~------~----~------~--~---
