Opened 4 years ago

Closed 2 years ago

#17473 closed New feature (wontfix)

Add like and ilike lookups

Reported by: pmartin Owned by: nobody
Component: Database layer (models, ORM) Version: 1.3
Severity: Normal Keywords:
Cc: Triage Stage: Design decision needed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Attachments (1)

patch.r17282 (4.7 KB) - added by pmartin 4 years ago.

Download all attachments as: .zip

Change History (7)

Changed 4 years ago by pmartin

comment:1 Changed 4 years ago by pmartin

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

comment:2 follow-up: Changed 4 years ago by russellm

  • Triage Stage changed from Unreviewed to Design decision needed

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.

comment:3 Changed 4 years ago by pmartin

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')

comment:4 Changed 4 years ago by pmartin

  • Has patch set

comment:5 in reply to: ↑ 2 Changed 4 years ago by pmartin

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

comment:6 Changed 2 years ago by aaugustin

  • Resolution set to wontfix
  • Status changed from new to closed

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.

Note: See TracTickets for help on using tickets.
Back to Top