so, some background, "table_name" is a Rails-style polymorphic association,
very old blog post about this at: http://techspot.zzzeek.org/archive/2007/05/1/
So there's a hacky way there, which you can adopt to declarative, to do the
"foreign key thats not really a foreign key" thing. I don't like it since
the lack of an FK means the relatoinship is entirely corruptable. From
reading the post you'll see i always find a way to use foreign keys correctly,
which means an extra table.
Polymorphically, youd have FooTag, BarTag, with a joined table, the table thats
joined has an FK back to the parent Foo or Bar.
Without using the polymorphic thing, which actually I usually don't for this,
you can have your tags @declared_attr pull out a "secondary" table, right in
the relationship there. Or polymorphically you could generate FooTag, BarTag
right there. @declared_attr is great for all that stuff.
The polymorphic association is something I'm slowly getting ready to re-present
in a declarative way, since its been coming up a lot lately and that blog post
is from 2007.
On Apr 22, 2011, at 12:20 AM, Andrey Petrov wrote:
> Hi there,
>
> I'm trying to make a table for annotating other tables' rows, perhaps even in
> relation to a user. I haven't been able to find similar examples of this
> elsewhere so I'd like a sanity check that I'm not doing something
> horrendously wrong. This is in SQLAlchemy 0.7 by the way.
>
> Here's an example:
>
> class Tag(BaseModel):
> __tablename__ = 'tag'
>
> id = Column(types.Integer, primary_key=True)
> table_name = Column(types.String(64))
> user_id = Column(types.Integer)
> row_id = Column(types.Integer)
>
> name = Column(types.String, nullable=False)
>
> Index('tag_backref_idx', Tag.table_name, Tag.user_id, Tag.row_id, Tag.name,
> unique=True)
> Index('tag_name_idx', Tag.table_name, Tag.user_id, Tag.name)
>
> So say I have a table called user, I want to be able to apply a tag to a row
> in it as Tag(table_name='foo', user_id=None, row_id=1, name='sometag'). The
> semantic is that user_id=None means it's a "global" (aka. system) tag,
> whereas a tag from a user would have that user_id filled respectively.
>
> On the Foo object, I can apply a relation like this (I'm doing this via a
> mixin class in my code):
>
> @declared_attr
> def tags(cls):
> return orm.relationship(Tag,
> primaryjoin=and_(
> Tag.table_name==cls.__tablename__,
> Tag.user_id==None,
> Tag.row_id==cls.id,
> ),
> foreign_keys=[Tag.table_name, Tag.user_id, Tag.row_id],
> )
>
> So now I can do things like...
>
> f = Session.query(Foo).get(1)
> f.tags # == [list of global Tag objects applied on object f]
>
> The goal is to be able to apply a "tag" on the system level (user_id=None) or
> on the user level for any row of any table (in reality there will be a couple
> of tables that will never have tags, but 90% will).
>
> My questions:
> Can we use the polymorphic_on stuff to make this better integrated?
> Is there a way to make a "dynamic" ForeignKey reference such that I can
> associate a tag to an object that hasn't been committed (ie. no `id` yet)?
>
> Example:
>
> f = Foo()
> t = Tag(name='blah', magic_triple_column_property=f)
> Session.add(f)
> Session.add(t)
> Session.commit()
>
> Am I crazy for trying to do this? Is there a better/simpler/more rational way
> to do something like this?
>
> Thanks in advance!
>
> - Andrey
>
> --
> 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.
--
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.
