Tortoise-orm: Case-insensitive equals

[gausie]

Is your feature request related to a problem? Please describe.
Since we have case-insensitive contains and case-insensitive starts/ends with, it would be really nice to just be able to case-insensitive compare!

Describe the solution you'd like
Perhaps value__iequals="ThE VaLuE"

Describe alternatives you've considered
Using startswith/endswith would obviously be error prone

[grigi]

This is an interesting request.

I did some quick research and found the following:

• MySQL is by default case insensitive, but may not be (depends on how the DB was setup). Enforcing case sensitivity can be enforced by special BINARY keyword. (Can be controlled by the COLLATE encoding on a per-column basis) Performance is best when using COLLATE.
• PostgreSQL is by default case sensitive, but has a fully functional LOWER() function, catch is it needs an index if you want to avoid a tablescan INDEX ON groups (LOWER(name))
• SQLite has incomplete support for for UPPER() or LOWER() (always a tablescan) and incomplete support for COLLATE NOCASE character encoding. Un-wildcarded LIKE also works. Only works for ascii characters, and other is always compared case sensitively. Performance info is extremely conflicting, but I suspect may be best COLLATE or LIKE?

It seems that for good performance (or any kind of predictability of function for MySQL), we should actually know if case-insensitive searches would be done at table-creation time.
The PostgreSQL implementation seems the simplest, and MySQL has the most environmental factors that can interfere, (or give us the functionality for free).

A generic, but non-optimal solution would be to do an unwildcarded LIKE (or ILIKE for PostgreSQL).

[hongquan]

PostgreSQL has CIText data type, which perform case-insensitive match. It would be nice if TortoiseORM support some way to define CICharField to make use of this data type.

[grigi]

Good point, we could also consider different DB types for the same field depending on use?

e.g. in PostgreSQL if we want a case-sensetive charfield with exact match only, we just use a regular B-tree index. if we want indexed LIKE, we use a different/extra index.
If we want it case insensitive we could use CIText or index on LOWER(field) (will have to evaluate which one works better).
If you want full-text searching, even more complicated indexes with collation.

And if one could indicate expected use of field, then we could generate a good DDL for it.

Still any such features are either going to take a while for me to get to, or you could do an initial PR to help get the ball rolling.

Still, proper support would likely need to consider #97 as well?

[grigi]

Basic support for this is in v0.13.7
Getting it to perform really well is for a future endeavour

[BoberMod]

I found iequals in doc of 0.16.13 https://tortoise-orm.readthedocs.io/en/latest/query.html?highlight=iequals#filtering
But I cant use it and got exception Unknown filter param 'username__iequals'. Allowed base values are ['id' ... 'username' ...].
I have installed 0.16.13, but it's not available.

So, which option is correct?

1. I'm using it incorrectly. But how is this possible, there is no explanation for this in the documentation.
2. The documentation is incorrect and tortoise still can't do a case-insensitive search.

[frankie567]

It seems the doc is wrong here. From what I see in the code, it seems the filter is called iexact, not iequals:

https://github.com/tortoise/tortoise-orm/blob/5627f11c7df4907d9b12dcd369769226a7b6219b/tortoise/filters.py#L345-L350

[bomzheg]

It seems the doc is wrong here. From what I see in the code, it seems the filter is called iexact, not iequals:

Thank you!!
I spent a few hours debugging

Developers, please bring the documentation in line!

[long2ice]

Oh, I never pay attention to that, thanks for that, I fixed docs now.