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 sqlalchemy@googlegroups.com 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 -~----------~----~----~----~------~----~------~--~---