Unexpected behavior with icontains in query filter

[Joel]

I'm trying to do a case insensitive search for a substring within a field in my model.

My model:

   

class doctor(models.Model):
        docid = models.AutoField(primary_key=True, unique=True) # Need autoincrement, unique and primary
        name = models.CharField(max_length=35)
        username = models.CharField(max_length=15)
        regid = models.CharField(max_length=15, default="", blank=True)
        photo = models.CharField(
            max_length=35, default="", blank=True)
        email = models.EmailField(default="", blank=True)
        phone = models.CharField(max_length=15)
        qualifications = models.CharField(
            max_length=50, default="", blank=True)
        about = models.CharField(
            max_length=35, default="", blank=True)
        specialities = models.CharField(
            max_length=50, default="", blank=True)
        department = models.CharField(max_length=50, default="ENT", blank=True)
        fees = models.FloatField(default=300.0)
        displayfee = models.IntegerField(default=0, blank=True)
        slotrange = models.CharField(max_length=50, blank=True)
        slotdurn = models.IntegerField(default=10)
        breakrange = models.CharField(
            max_length=50, default="", blank=True)
        slotsleft = models.CharField(
            max_length=50, default="", blank=True)
        def __str__(self):
            return self.name
        def Range(self):
            return self.slotrange
        def listslots(self):
            SlotRange = self.slotrange
            SlotDurn = self.slotdurn
            startime = SlotRange.split('-')[0]
            endtime = SlotRange.split('-')[1]
            sthr, stmin = SplitTimeString(startime)
            enhr, enmin = SplitTimeString(endtime)
            print(stamptoday(sthr, stmin))
            print(stamptoday(enhr, enmin))
            startstamp = stamptoday(sthr, stmin)
            endstamp = stamptoday(enhr, enmin)
            secdurn = SlotDurn*60
            slotlist = []
            for sec in range(startstamp, endstamp, secdurn):
                enttime = sec + secdurn
                myrange = ("%s - %s" % (HumanTime(sec),
                                        HumanTime(enttime)))
                slotlist.append(myrange)
            return slotlist

Under the field 'name' in my mysql database, are two rows with values for name as 'Joel' and 'Jaffy Joel'.

When I do the search like this:

   

from appointments.models import customer, doctor, appointment
    doctor.objects.filter(name__icontains='joel')

Output:

   

<QuerySet []>

But when I do:

    doctor.objects.filter(name__icontains='Joel')

Output:

   

 <QuerySet [<doctor: Joel>, <doctor: Jaffy Joel>]>

Why isnt the case insensitive search working for a lowercase search?

I'm on django 2.0.7

[Matthew Pava]

I’m fascinated by this problem.

Try this workaround.

https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#lower

 

Register the lookup Lower like so:

CharField.register_lookup(Lower, "lower")

 

Then use the contains lookup.

doctor.objects.filter(name__lower__contains="joel")

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/46579c92-8a12-4977-814c-9c3fcaa14711%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jason]

Are you using sqlite for your db?  if so, there are some notes about case insensitive matching at https://docs.djangoproject.com/en/dev/ref/databases/#sqlite-string-matching

otherwise, I'd be interested in seeing what your SQL is like

in your django shell, do the following:

doctors = Doctors.objects.filter(name__icontains = 'joel')

print(doctors.query)

It should have something like 

SELECT ... FROM ... WHERE name ILIKE  '%joel%';

which should do the case insensitive search.

Otherwise, this is a database issue, and not a django issue.  Django just converts the filter call to the appropriate SQL and has the db execute it.

[ireoluwa fakeye]

The only reasonable explanation is Django sees only upper case inputs as being case sensitive .so inputting  a lower case and specifying icontains wouldnt change anything .you could get your input from a form to confirm

--

[Joel Mathew]

This is what I got:

In [8]: doct = doctor.objects.filter(name__icontains = 'joel')
...: print(doct.query)

SELECT `appointments_doctor`.`docid`, `appointments_doctor`.`name`,
`appointments_doctor`.`username`, `appointments_doctor`.`regid`,
`appointments_doctor`.`photo`, `appointments_doctor`.`email`,
`appointments_doctor`.`phone`, `appointments_doctor`.`qualifications`,
`appointments_doctor`.`about`, `appointments_doctor`.`specialities`,
`appointments_doctor`.`department`, `appointments_doctor`.`fees`,
`appointments_doctor`.`displayfee`, `appointments_doctor`.`slotrange`,
`appointments_doctor`.`slotdurn`, `appointments_doctor`.`breakrange`,
`appointments_doctor`.`slotsleft` FROM `appointments_doctor` WHERE
`appointments_doctor`.`name` LIKE %joel%


Sincerely yours,

Dr Joel G Mathew

> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-users.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/django-users/2b8dd9eb-b1ab-4eaf-ac17-4f03c1ed1c2c%40googlegroups.com.

[Joel Mathew]

This workaround works.

In [10]: from django.db.models.functions import Lower
In [11]: from django.db.models import CharField

In [12]: CharField.register_lookup(Lower, "lower")
Out[12]: django.db.models.functions.base.Lower

In [13]: doctor.objects.filter(name__lower__contains="joel")
Out[13]: <QuerySet [<doctor: Joel>, <doctor: Jeslin Joel>]>

I'm eager to solve this strange behavior though.

Sincerely yours,

Dr Joel G Mathew

> https://groups.google.com/d/msgid/django-users/c204f799406943208eaabb1ece752054%40ISS1.ISS.LOCAL.

[Jason]

Check out https://code.djangoproject.com/ticket/9682.  Apparently this is a mysql specific thing.