#251 closed enhancement (fixed)
[patch] Add "AND" and "OR" clauses to the SQL query syntax
Reported by: | Owned by: | hugo | |
---|---|---|---|
Component: | Metasystem | Version: | |
Severity: | normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
After discussion on IRC about the undocumented _or syntax, which Adrian didn't like (see http://loglibrary.com/show_page/view/179?Multiplier=3600&Interval=6&StartTime=1122875881 and http://loglibrary.com/show_page/view/179?Multiplier=3600&Interval=6&StartTime=1122989955), a few ideas were tossed around. We ended up settling on having two "magic" functions, tentatively to live in the meta package and be named meta.AND and meta.OR, which will be called as follows:
polls.get_list(pub_date__exact=datetime.now(), clause=meta.OR(question__startswith='a', question__startswith='b')) # Yields "SELECT * FROM polls WHERE pub_date=NOW AND ((question LIKE 'a%') OR (question LIKE 'b%'))"
The functions can also be nested:
table.get_list(clause=meta.OR(field1__exact=10, clause=meta.AND(field1__gte=20, field1__lte=29))) # Yields "SELECT * FROM table WHERE field1=10 OR ((field1 >= 20) AND (field1 <= 29))"
They can also be invoked multiple times in the same get_list() call. Since you can't repeat exactly the same kwarg twice, anything starting with "clause" will be accepted, thus:
table.get_list(field1__exact=5, clause1=meta.OR(field2__startswith='a', field2__startswith='b'), clause2=meta.OR(field3__startswith='a', field3__startswith='b')) # Yields "SELECT * FROM table WHERE field1=5 AND ((field2 LIKE 'a%') OR (field2 LIKE 'b%')) AND ((field3 LIKE 'a%') OR (field3 LIKE 'b%'))"
To avoid any possible conflict with a field name starting with "clause", what follows the word "clause" must be composed of nothing but digits. That way something like "clause__exact='foo'
" would still be treated as a search clause, but "clause1=meta.OR(...)
" would be treated as an OR.
I'll write a patch to implement this idea.
Attachments (4)
Change History (25)
by , 19 years ago
Attachment: | and_or_clauses.patch added |
---|
comment:1 by , 19 years ago
Summary: | Add "AND" and "OR" clauses to the SQL query syntax → [patch] Add "AND" and "OR" clauses to the SQL query syntax |
---|
The patch turned out to be quite simple; I've also written some unit tests for the functionality and tested it against PostgreSQL.
comment:2 by , 19 years ago
Oops. The and_or_clauses.r434.patch I just uploaded includes an irrelevant section from a different patch. Ignore it -- I'll upload a fixed version.
by , 19 years ago
Attachment: | and_or_clauses.r434.fixed.patch added |
---|
Fixed version of r434 patch, with irrelevant section removed
comment:3 by , 19 years ago
Status: | new → assigned |
---|
comment:4 by , 19 years ago
milestone: | → Version 1.0 |
---|
comment:5 by , 19 years ago
This is just me thinking loud, but would it not look a lot clearner of you could do something like this:
foo.get_list(limit=5. (bar__lt=5 OR bar__gt=10), qwerty__exact=2)
comment:6 by , 19 years ago
Yup, that would definitely look cleaner. Unfortunately, it's not valid Python syntax...
comment:7 by , 19 years ago
Perhaps a custom infix operator could be used, as described here: http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/384122
Then you could use something like
foo.get_list(limit=5, (bar__lt=5 |meta.OR| bar__gt=10), qwerty__exact=2)
The infix operators meta.OR
and meta.AND
could create "clauses" as described in the ticket, but the syntax is much nicer.
comment:8 by , 19 years ago
I think all the keyword arguments would have to be after the clauses.
Other than that, it looks good.
comment:9 by , 19 years ago
What I did in one of my projects in a similar situation was to introduce a Q() function that just encapsulates a condition in an object. Then I defined or and and on those objects, so that I could write stuff like (translated to django syntax):
foo.get_list(meta.Q(title__contains='foo') || meta.Q(title_contains='bar'), limit=5)
and && just produces new Q() objects and a getSQL method on that object just produces the needed where clause. |
comment:10 by , 19 years ago
Oh, and it's by far less hacky than the pseudo-infix operators that will break in many colourful ways if you drop out one of those | :-)
comment:11 by , 19 years ago
After digging into the source a bit, I think the _parse_lookup function is what would be needed to be called in the __init__
of the Q objects to produce an SQL internally. The __or__
and __and__
methods will just combine the Q objects to produce the combined SQL (and update table counts and stuff like that to have correct joins).
comment:13 by , 19 years ago
(thought about it more)
Esaj's thing wouldn't actually work, because inside the bracket things like bar__lt=5
would just be assignments which are statements, not expressions. They wouldn't be kwargs anymore...
Hugos plan looks more reasonable.
comment:15 by , 19 years ago
Owner: | changed from | to
---|---|
Status: | assigned → new |
I'll first have to write it ;-) - I have something similar in another project (a hierarchical database for python), but it won't be hard to write it, I think. I'll attach a patch as soon as I have it done.
comment:16 by , 19 years ago
Status: | new → assigned |
---|
comment:17 by , 19 years ago
Ok, here is my first take at the problem. It implements the following syntax:
from django.models.cms import pages from django.core.meta import Q print pages.get_list(complex=( Q(title__contains='word1') | Q(title__contains='word2') | Q(title__contains='word3') ))
Queries can be stored in variables and later combined with any other query - the combination of queries is non-destructive, so you could do things like this:
q = Q(title__contains='word1') q |= Q(title__contains='word2') print pages.get_list(complex=(q | Q(title__contains='word3')))
Queries are table-agnostic - they look at tables only if their getSQL method is called and that uses internally the _parse_lookup stuff, so it handles all the exact same stuff that the original model handles. If you give both a complex query and any other lookup argument, they will be combined with AND (just like any other query parameter). And you can use complex within Q objects.
I didn't do extensive tests nor did I add unittests - family business today, so only limited time. But hopefully somebody else can take a banging on the code and provide unittests.
comment:18 by , 19 years ago
Ok, and of course you can do stuff like:
Q(...)&Q(...)|Q(...)&Q(...)
(using explicit grouping with parens, too)
comment:19 by , 19 years ago
Resolution: | → fixed |
---|---|
Status: | assigned → closed |
comment:20 by , 18 years ago
There is an error in the example. Instead of
from django.core.meta import Q
it should be
from django.db.models.query import Q
It is also not clear how to create "NOT AND"... Maybe or ! operator could be added? E.g.
Q(...) & Q(...) | (Q(...) Q(...))
Patch against r390 to add meta.AND and meta.OR functionality; tests included