[patch] Add "AND" and "OR" clauses to the SQL query syntax
|Reported by:||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 12 years ago by
|Summary:||Add "AND" and "OR" clauses to the SQL query syntax → [patch] Add "AND" and "OR" clauses to the SQL query syntax|
comment:15 Changed 11 years ago by
|Owner:||changed from Adrian Holovaty to hugo|
|Status:||assigned → new|