Opened 10 years ago

Closed 10 years ago

Last modified 8 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: UI/UX:

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@… 10 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@… 10 years ago.
Patch updated to r434
and_or_clauses.r434.fixed.patch (3.2 KB) - added by rmunn@… 10 years ago.
Fixed version of r434 patch, with irrelevant section removed
q.diff (3.6 KB) - added by hugo 10 years ago.
query algebra for complex queries

Download all attachments as: .zip

Change History (25)

Changed 10 years ago by rmunn@…

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

comment:1 Changed 10 years ago by rmunn@…

  • Summary changed from Add "AND" and "OR" clauses to the SQL query syntax to [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.

Changed 10 years ago by rmunn@…

Patch updated to r434

comment:2 Changed 10 years ago by 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.

Changed 10 years ago by rmunn@…

Fixed version of r434 patch, with irrelevant section removed

comment:3 Changed 10 years ago by adrian

  • Status changed from new to assigned

comment:4 Changed 10 years ago by adrian

  • milestone set to Version 1.0

comment:5 Changed 10 years ago by 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)

comment:6 Changed 10 years ago by adrian

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

comment:7 Changed 10 years ago by 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.

comment:8 Changed 10 years ago by rjwittams

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

comment:9 Changed 10 years ago by 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.

comment:10 Changed 10 years ago by 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 | :-)

comment:11 Changed 10 years ago by 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).

comment:12 Changed 10 years ago by eugene@…

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

comment:13 Changed 10 years ago by 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.

comment:14 Changed 10 years ago by adrian

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

comment:15 Changed 10 years ago by hugo

  • Owner changed from adrian to hugo
  • Status changed from assigned to 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 Changed 10 years ago by hugo

  • Status changed from new to assigned

Changed 10 years ago by hugo

query algebra for complex queries

comment:17 Changed 10 years ago by 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.

comment:18 Changed 10 years ago by hugo

Ok, and of course you can do stuff like:

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

(using explicit grouping with parens, too)

comment:19 Changed 10 years ago by adrian

  • Resolution set to fixed
  • Status changed from assigned to closed

(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 Changed 9 years ago by 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(...))

comment:21 Changed 8 years ago by anonymous

  • milestone Version 1.0 deleted

Milestone Version 1.0 deleted

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