[patch] Add "AND" and "OR" clauses to the SQL query syntax

[rmunn@…]

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.

[rmunn@…]

Patch against r390 to add meta.AND and meta.OR functionality; tests included

[rmunn@…]

The patch turned out to be quite simple; I've also written some unit tests for the functionality and tested it against PostgreSQL.

[rmunn@…]

Patch updated to r434

[rmunn@…]

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.

[rmunn@…]

Fixed version of r434 patch, with irrelevant section removed

[EspenG]

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)

[Adrian Holovaty]

Yup, that would definitely look cleaner. Unfortunately, it's not valid Python syntax...

[Esaj]

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.

[rjwittams]

I think all the keyword arguments would have to be after the clauses.
Other than that, it looks good.

[hugo]

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)

It's not exactly what Espen wrote, but I think it's near enough. And you get the AND-before-OR grouping for free. Combination of Q() objects by

and && just produces new Q() objects and a getSQL method on that object just produces the needed where clause.

[hugo]

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 | :-)

[hugo]

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).

[eugene@…]

Looks like hugo solved the problem. Looks clean and natural to me.

[rjwittams]

(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.

[Adrian Holovaty]

Hugo -- that looks great. Could you upload the patch?

[hugo]

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.

[hugo]

query algebra for complex queries

[hugo]

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.

[hugo]

Ok, and of course you can do stuff like:

Q(...)&Q(...)|Q(...)&Q(...)

(using explicit grouping with parens, too)

[Adrian Holovaty]

(In [1508]) Fixed #251 -- Added OR support to queries, via the new 'complex' DB API keyword argument. Updated docs and added unit tests. Also removed old, undocumented '_or' parameter. Thanks, Hugo.

[Jarosław Zabiełło]

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(...))

[(none)]

Milestone Version 1.0 deleted