i have asked about similar thing (e.g. some filter_or construct) and there's nothing so far that can do ORs or UNIONs - or any other query arithmetics except AND. See that thread (last week), there were some workarounding suggestions, using left outer join or so. On Wednesday 21 May 2008 18:20, Kirk Strauser wrote: > 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!
--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
