automatic company_id insertion

[sjo...@congressus.nl]

Hi,

I have one set of tables and I want to automate the insertion of the company_id in queries at certain tables. So I need to define in the models which tables are effected, no worries there, but where do I need to implement the query manipulation? The SQLAlchemy object is shared between different requests (as the configuration remains te same). But the session is different, as the company_id differs between sessions.

Where and how do I implement the query manipulation?!

Thanks

[Michael Bayer]

its not clear what usage pattern you're looking for here.   Lets start with the obvious, putting "company_id" in a query:

session.query(SomeObject).filter(SomeObject.company_id == 5)

easy enough.

Now what exactly do you want it to look like?

Like,

session_one.query(SomeClassOne)  -> automatically put company_id = 8

        session_two.query(SomeClassTwo)   -> automatically put company_id = 15

?

if I'm given "session_one" and "SomeClassOne", what is the "company_id" for that ?    

There's a pattern for auto-querying of a certain column at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery .   But this doesn't get into how to determine where the custom criterion would come from.

[sjo...@congressus.nl]

Thanks for your guided questions and the example pattern. Indeed the basic query would be

session.query(SomeObject).filter(SomeObject.loginname.like("example")).filter(SomeObject.company_id == SomeHTTPSessionGlobal.company_id)

As this query is extension is used many times in my codebase (due to the multi-tentancy nature of the concept) I want to automate this. I have implemented and new Query-class;

session = sessionmaker(bind=engine, query_cls=MyQuery)

In this MyQuery two things need to happen; A) check whether the queried table has the certain company_id field and B) automatically implement the additional filter to add the SomeHTTPSessionGlobal.company_id (which is based on the sub-domain)
=> Is by altering the get(), __ite__ and from_self() functions enough to effect all queries?
=> How to get the adjusted table and detect whether an company_id field exists?

In such that when the SomeObject has an field company_id, it automatically applies an additional filter. So that my query in the code may be;

session.query(SomeObject).filter(SomeObject.loginname.like("example"))

Thanks

 

[Michael Bayer]

On Jan 3, 2013, at 3:17 PM, sjo...@congressus.nl wrote:

In this MyQuery two things need to happen; A) check whether the queried table has the certain company_id field and B) automatically implement the additional filter to add the SomeHTTPSessionGlobal.company_id (which is based on the sub-domain)
=> Is by altering the get(), __ite__ and from_self() functions enough to effect all queries?
=> How to get the adjusted table and detect whether an company_id field exists?

the vast majority of queries go through __iter__(), so that's probably all you need.    from_self() and get() are not as common - though get() will be used for a many-to-one lazy load in most cases, if this class is the target of a many-to-one relationship().

If you're dealing with mapped classes, a simple enough check is hasattr(MyClass, 'company_id').    Within Query, calling _mapper_zero().class_ gives you this class, assuming the query is against a single full entity like query(MyClass).

[sjo...@congressus.nl]

It is working, thanks for your help and additional explanation!