I'm developing a Django site that uses a lot of pre-existing
SQLAlchemy tables to generate reports, etc. I'm trying to get my head
around the best way to programmatically generate some fairly complex
queries.
For instance, our users may have several roles assigned to them, and
the data they can see depends on the roles they have. What I planned
to do was something along the lines of:
results = []
for role in usersroles:
query = session.query(DataTable)
if role.field1value:
query = query.filter_by(field1=field1value)
if role.field2value:
query = query.filter_by(field2=field2value)
if role.field3value:
query = query.filter_by(field3=field3value)
results.extend(query.all())
That's really ugly for a few reasons, though. It's possible (and
likely) that the same record might be returned by the results of
several different queries, so I'd have to use a set or similar to weed
out duplicates. Also, if the first role's query results in a million
records and the second role's query gives the same million records,
then I'm moving a whole lot of data for no good reason.
Now, Django's built-in ORM supports using "|" to merge the output of
several different queries, and it does this by actually generating
generating the appropriate SQL. For example:
>>> models.Role.objects.filter(pk=1)
[<Role: foo>]
>>> models.Role.objects.filter(pk=2)
[<Role: bar>]
>>> models.Role.objects.filter(pk=2) | models.Role.objects.filter(pk=1)
[<Role: bar>, <Role: foo>]
>>> print (models.Role.objects.filter(pk=1) |
models.Role.objects.filter(pk=2)).query
SELECT "user_role"."id", "user_role"."name" FROM "user_role" WHERE
"user_role"."id" = 1 OR "user_role"."id" = 2 ORDER BY
"user_role"."name" ASC
Is there anything similar in SQLAlchemy? If not, is there an
idiomatic approach for this? Thanks!
--
Kirk Strauser
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
