[patch] Add "AND" and "OR" clauses to the SQL query syntax
|Reported by:||rmunn@…||Owned by:||hugo|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
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.
Change History (25)
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
comment:15 Changed 9 years ago by hugo
- Owner changed from adrian to hugo
- Status changed from assigned to new
Changed 9 years ago by hugo
comment:19 Changed 9 years ago by adrian
- Resolution set to fixed
- Status changed from assigned to closed