Hi All,
We're looking to add tags to a number of 'entities' within our
application. A simplified data structure would be:
Image:
id
file
title
description
Article:
id
text
Tag:
id
value
Entity_tags:
id
entity_type ('image' or 'article'
entity_id (PK of the image/article table)
tag_id
We've got the following (highly simplified) structure:
image_table = Table('image', meta.metadata,
Column('id', types.Integer, primary_key=True), )
article_table = Table('article', meta.metadata,
Column('id', types.Integer, primary_key=True), )
tag_table = Table('tag', meta.metadata,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode(50), unique=True), )
entity_tag_table = Table('entity_tag', meta.metadata,
Column('tag_id', types.Integer, ForeignKey(tag_table.c.id)),
Column('entity_type', types.String, nullable=False),
Column('entity_id', types.Integer, nullable=False),
)
# And the ORM Mappings:
class Image(object): pass
class Article(object): pass
class Tag(object): pass
class EntityTag(object): pass
orm.mapper(Image, image_table, properties={
'tags': orm.relation(Tag, secondary=entity_tag_table,
primaryjoin=and_(image_table.c.id==EntityTag.entity_id,
EntityTag.entity_type=='image'),
secondaryjoin=EntityTag.tag_id==Tag.id,
foreignkeys=[EntityTag.entity_id, EntityTag.tag_id], ),
})
orm.mapper(Article, article_table, properties={
'tags': orm.relation(Tag, secondary=entity_tag_table,
primaryjoin=and_(article_table.c.id==EntityTag.entity_id,
EntityTag.entity_type=='article'),
secondaryjoin=EntityTag.tag_id==Tag.id,
foreignkeys=[EntityTag.entity_id, EntityTag.tag_id], ),
})
When we append to the image.tags collection, the entity_tag table needs
to know that the entity_type is 'image', but we can't seem to set that.
What is the standard way of dealing with this problem?
Is there the concept of a generic foreign key in SQLAlchemy?
Does anyone know if this database pattern has a formal name?
TIA,
Jim
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
