Search code examples
pythondjangodjango-modelsdjango-orm

different results with icontains in django ORM

I'm filtering results from django database (mysql) with the following:

queryset = MyModel.objects.filter(value_name__icontains=search).order_by("pk")


class MyModel(models.Model):
    ...
    value_name = models.CharField(unique=True, max_length=255)
    ...

    class Meta:
        managed = False
        db_table = "my_table"

The collation value for field value_name=utf8mb4_0900_bin

When the value of search = "bin" I'm getting one set of results, Results are having substring "bin" in them.

and when the value of search="Bin" i'm getting another set of results. Results are having substring "Bin" in them.

The results of both of these don't have any intersection.

It looks like a collation issue, how to fix this? I can't change anything on the database, as I only have read access.

My django version is 3.2.

I tried this but it did not work.

value_name = models.CharField(unique=True, max_length=255, db_collation='utf8_general_ci')

Solution

  • I suspect it is the collation.

    Your table has managed=False, so changing the field definition on the model to set the collation won't help you. You need to check the table definition in the actual database - if the collation is not defined there, it won't work.