Opened 19 years ago

Closed 18 years ago

Last modified 17 years ago

#251 closed enhancement (fixed)

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

Reported by: rmunn@… 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)

and_or_clauses.patch (5.0 KB ) - added by rmunn@… 19 years ago.
Patch against r390 to add meta.AND and meta.OR functionality; tests included
and_or_clauses.r434.patch (4.5 KB ) - added by rmunn@… 19 years ago.
Patch updated to r434
and_or_clauses.r434.fixed.patch (3.2 KB ) - added by rmunn@… 19 years ago.
Fixed version of r434 patch, with irrelevant section removed
q.diff (3.6 KB ) - added by hugo 18 years ago.
query algebra for complex queries

Download all attachments as: .zip

Change History (25)

by rmunn@…, 19 years ago

Attachment: and_or_clauses.patch added

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

comment:1 by rmunn@…, 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.

by rmunn@…, 19 years ago

Attachment: and_or_clauses.r434.patch added

Patch updated to r434

comment:2 by rmunn@…, 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 rmunn@…, 19 years ago

Fixed version of r434 patch, with irrelevant section removed

comment:3 by Adrian Holovaty, 19 years ago

Status: newassigned

comment:4 by Adrian Holovaty, 18 years ago

milestone: Version 1.0

comment:5 by EspenG, 18 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 Adrian Holovaty, 18 years ago

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

comment:7 by Esaj, 18 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 rjwittams, 18 years ago

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

comment:9 by hugo, 18 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)
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.

comment:10 by hugo, 18 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 hugo, 18 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:12 by eugene@…, 18 years ago

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

comment:13 by rjwittams, 18 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:14 by Adrian Holovaty, 18 years ago

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

comment:15 by hugo, 18 years ago

Owner: changed from Adrian Holovaty to hugo
Status: assignednew

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 hugo, 18 years ago

Status: newassigned

by hugo, 18 years ago

Attachment: q.diff added

query algebra for complex queries

comment:17 by hugo, 18 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 hugo, 18 years ago

Ok, and of course you can do stuff like:

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

(using explicit grouping with parens, too)

comment:19 by Adrian Holovaty, 18 years ago

Resolution: fixed
Status: assignedclosed

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

comment:20 by Jarosław Zabiełło, 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(...))

comment:21 by (none), 17 years ago

milestone: Version 1.0

Milestone Version 1.0 deleted

Note: See TracTickets for help on using tickets.
Back to Top