Add like and ilike lookups

[Pablo Martín]

Add like and ilike ​lookups.

Should be possible to do something like this:

    MyModel.objects.filter(field_name__like='xx%YY%zz')
    MyModel.objects.filter(field_name__ilike='xx%yy%zz')
    User.objects.filter(username__like='a%in')

Instead of:

    MyModel.objects.filter(field_name__regex='^xx.*YY.*zz$')

It is more quick and more readable.

I create the next ​app, after read the next ​question

I didn't understand why this is not coded

[Pablo Martín]

​Pull request

[Russell Keith-Magee]

On the whole, I'm not convinced that this proposal is beneficial.

I'm not sure I agree that LIKE syntax is more readable. Regular expressions are a more powerful search syntax, and they are a syntax that is familiar in many other contexts. In contrast, LIKE syntax has limited capabilities, and is available only in SQL. In addition, Django's ORM tries to be avoid exposing SQL-specific features as much as possible, and LIKE syntax is about as SQL specific as you can get.

I'd also need to see evidence that a LIKE query is faster than the analogous regex-based query, for all databases. There is evidently ​a big difference under MySQL, but the same apparently ​can't be said about Postgres. Personally, I'm not inclined to add a feature duplicating existing functionality just to work around deficiencies in MySQL's indexing mechanisms.

Marking Design Decision Needed because I'm not *fundamentally* opposed to the idea; however, unless someone from the core is particularly enamoured with the idea, I think it's a wontfix.

As an aside, I *would* be in favour of anything that would make it easier for you to add this filter clause as a third party extension. It *can* be done right now, but it should be much easier to drop in custom query clauses.

[Pablo Martín]

I think the next, if exists:

• contains: LIKE %value%
• startswith: LIKE value%
• endswith: LIKE %value

And the same with icontains, istartswith and iendswith. May exist some lookup that complete the syntax. It's possible that "like" is not the name more appropriate, I am bad with the names, you can call otherwise.

You said that: "I'm not inclined to add a feature duplicating existing functionality" and this is not true. If you think that, you should removed the before lookups (contains, startswith, endwith), exact and iexact. But these get a readable code.

It is true that in postgres is a litle better regex, but this is only a case, you should not think of specific cases. But if you think that this is a reason to don't accept it, you should code some like this:

    User.objects.filter(username__regex='.*foo.*')
    User.objects.filter(username__regex='.*foo')

Instead of

    User.objects.filter(username__contains='foo')
    User.objects.filter(username__endswith='foo')

[Pablo Martín]

Replying to russellm:

I have written a ​post about this:

• I have written a ​benchmark, with this you can see the improvement.
• In this post, I talk that it is not a duplication of code or duplication of funcionality. You can read. I hope to convince you

[Aymeric Augustin]

I agree with Russell, the best way forward is to make custom lookups easier to define, and there's some interest in this topic at the moment.