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
867	[PATCH] Add new (negation) lookup types (e.g., doesnotstartwith, doesnotcontain, etc.)	Drew Amato <drewamato@…>	Adrian Holovaty	"This proposed patch adds new lookup types to the DB API, allowing negation of existing string-based lookups. (I asked Adrian about it in IRC last night, and he seemed open to suggestions.) I've only done this because I needed the functionality myself, but it seems to me to enhance the completeness of the DB lookup types (and I would have thought it would be a fairly common need.)

Seven new lookup types are added: ''inotexact'', ''doesnotcontain''/''idoesnotcontain'', ''doesnotstartwith''/''idoesnotstartwith'', and ''doesnotendwith''/''idoesnotendwith''.

The only difference between these lookup types and their normal counterparts, is that these turn into a ""NOT LIKE"" SQL statement, instead of a ""LIKE"".

I'm not hugely happy with the names (particularly ''inotexact''), so if anyone has better ideas then please change them. (I know they're quite long, but the other lookup keywords seem to be chosen for gramattical correctness/readability rather than succinctness anyway.)

I added ''inotexact'' to complement ''iexact'' (as a case-insensitive LIKE), but I seem to have some problems (see below.) Also, there's no need for a negation to ''exact'', since we already have ''ne''.

Here's how they look in real life:
{{{
>>> polls.get_list()
[How much do you like Django?, What's up?, To be or not to be., What to do.]

>>> polls.get_list(question__doesnotcontain='To')
[How much do you like Django?, What's up?, What to do.]

>>> polls.get_list(question__idoesnotcontain='To')
[How much do you like Django?, What's up?]

>>> polls.get_list(question__doesnotstartwith='what')
[How much do you like Django?, What's up?, To be or not to be., What to do.]

>>> polls.get_list(question__idoesnotstartwith='what')
[How much do you like Django?, To be or not to be.]

>>> polls.get_list(question__doesnotendwith='?')
[To be or not to be., What to do.]
}}}

I wanted to add ''inotexact'' to be the complement of ''iexact'' (which should be different than ''ne'', based on case-sensitivity) but it looks like ''ne'' (at least with MySQL) is case-insensitive anyway. (?!) I don't know if this applies to other backends.
{{{
>>> polls.get_list(question__ne='what\'s up?')
[How much do you like Django?, To be or not to be., What to do.]

>>> polls.get_list(question__inotexact='what\'s up?')
[How much do you like Django?, To be or not to be., What to do.]
}}}

Although the patch will affect the other backends, I have only been able to test it on MySQL, since I don't have access to the others. (Any confirmation would obviously be appreciated.) For the paranoid, I have double-checked that all the other backends do support ""NOT LIKE"" syntax: [http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html MySQL], [http://www.postgresql.org/docs/7.4/interactive/functions-matching.html#FUNCTIONS-LIKE PostgreSQL], [http://www.sqlite.org/lang_expr.html SQLite], [http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_la-lz_115x.asp MSSQL], so AFAIK they should work with this patch.

"	enhancement	closed	Database layer (models, ORM)	dev	trivial	fixed			Unreviewed	0	0	0	0	0	0