dynamically adjusting the sql query that the model/class is defined by

[Damian Dimmich]

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?

How would I generate select based classes 'on the fly'/on a per request
basis (and would this be very slow if I did)?

Thank you very much in advance for any suggestions.

Damian

[Michael Bayer]

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.

[Damian]

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