id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
251	"[patch] Add ""AND"" and ""OR"" clauses to the SQL query syntax"	rmunn@…	hugo	"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."	enhancement	closed	Metasystem		normal	fixed			Accepted	1	0	0	0	0	0