On Jan 21, 2013, at 8:41 AM, Damian Dimmich wrote:
> Hi
>
> I have an application that was originally written to support just one
> organisation. This app is now being modified to support multiple
> organisations. As such I am trying to update the main bits of the model to
> always filter queries by organisation_id, a new column I have added in to the
> relevant tables.
>
> For example, a class Users, used for authentication would have (so the
> login/logout/reset password functions would be modified to use this):
>
> class AllUser(Base):
> __tablename__ = 'users'
> id = sa.Column(sa.Integer, primary_key=True)
> username = sa.Column(sa.Unicode(15), nullable=False, unique=True)
> organisation_id = sa.Column(sa.Integer, sa.ForeignKey('organisation.id'))
> organisation = sa.orm.relationship('Organisation')
>
>
> and the 'restricted' version would just show users from the organisation (all
> other existing parts of the application would use this class for its queries)
> :
>
> class User(Base):
> __table__ =
> select([users_table]).where(users_table.c.organisation_id==session['organisation_id'])
> id = sa.Column(sa.Integer, primary_key=True)
> username = sa.Column(sa.Unicode(15), nullable=False, unique=True)
> organisation_id = sa.Column(sa.Integer, sa.ForeignKey('organisation.id'))
> organisation = sa.orm.relationship('Organisation')
>
> where session['organisation_id'] is the organisation id of the currently
> logged in user.
>
> Is this a sensible approach? Are there any concurrency issues I should be
> aware of such as: would the __table__ select statement be cached/reused from
> one request to the next?
well yeah that actually wouldn't work at all, __table__ = select()... is
evaluated immediately, so whatever is in "session['org_id']" at that point is
baked into the query.
You can make it work if you use a callable there ("callable_" with bindparam()
is SQLAlchemy 0.8):
__table__ = select(...).where(table.c.org_id == bindparam(None,
callable_=lambda: session['organization_id']))
but why is it important that the query here be broken up at the class level ?
The only time the difference between AllUser and User has any significance is
when you say:
query(AllUser) / query(User)
why is that better than just saying:
query(User) / query(User).filter(User.with_session_organization)
? (an expression like "User.with_session_organization" can be created using a
hybrid attribute:
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/hybrid.html)
unless it's your aim to create a relationship() that refers to User. In which
case you can also add that bindparam() idea to the "primaryjoin" condition of
relationship. An example that uses this technique is here:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GlobalFilter though if you
had a callable_ inside the bindparam(), you wouldn't need the "options" used in
that example.
--
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.
