Hello Michael,
Thank you for the speedy response! bindparam was exactly what I was
looking for. While I can go through the entire application and try to make
sure each query is accounted for/has the filter added, I think it more
likely to catch all of them by applying the filter at the model level where
appropriate. With the bindparam option, all I needed to modify was the
model, and the authentication bits - and from what I can tell, the rest now
behaves correctly. As you predicted, the first version I wrote ended up
having the first organisation used baked into the entire model.
Thank you again for your support,
Damian
On Monday, 21 January 2013 16:07:15 UTC, Michael Bayer wrote:
>
>
> 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 view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/USQ5xoTMwFsJ.
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.
