Use of the LIKE operator

[david-alm]

Hello 👋

After trawling through the code, trying to figure out how to perform a prefix search, I've realized that this this dictionary maps orm operators to sqlalchemy methods. Note that iexact, contains and icontains reference some variant of like.
Meanwhile, this code escapes the search string when using like - but only for the operators contains and icontains.

So to my knowledge:

• There's no way to search text using the case sensitive LIKE operator (PR: Feature: Adding a LIKE operator #87)
• iexact is buggy - since it doesn't escape wildcards; for instance find% matches find_the_second_bug (PR: Fixing iexact behaviour #86)

[david-alm]

Here's a quick repro of the issue:

import databases
import orm
import sqlalchemy

database = databases.Database("sqlite:///db.sqlite")
metadata = sqlalchemy.MetaData()


class Note(orm.Model):
    __tablename__ = "notes"
    __database__ = database
    __metadata__ = metadata

    id = orm.Integer(primary_key=True)
    text = orm.String(max_length=100)
    completed = orm.Boolean(default=False)

# Create the database
engine = sqlalchemy.create_engine(str(database.url))
metadata.create_all(engine)

# Create three somewhat similar notes
await Note.objects.create(text="Find the first bug", completed=True)
await Note.objects.create(text="Find the second bug", completed=False)
await Note.objects.create(text="find_the_second_bug", completed=False)

# Search for `find%`
await Note.objects.filter(text__iexact="find%").all() 
# This returns all 3 notes, even though none of them is "lowercase exact" `find%`

# In the same vein, this occurs with `_` as well
await Note.objects.filter(text__iexact="find_the_second_bug").all() 
# This returns the last 2 notes.