Feature request: filter like and ilike

[usernein]

Example of usage:

users = await User.filter(first_name__ilike="c%r")

I know this can be done with startswith and endswith, but there are cases where using the LIKE operator is better:

users = await User.filter(first_name__ilike="j_r%")

This would be used to match Jeremy, Jorge, Jordan...

[WisdomPill]

I would like to work on it this weekend, @abondar can you assign it to me please?

[grigi]

@WisdomPill You don't need it assigned to you if you want to work on it. Just state you want to?

I thought about this and SQL Injection abilities, differentiating between _(the match operator) and \_(the literal) is going to be hard. We would need to keep track of \_ and \% separately from other \'s, else we open ourselves up for SQL-Injection.

I think we can do it safely, but will definitely have to apply lots of scrutiny/testing to this.

[WisdomPill]

Okay, I thought that because there are projects on this repository that assignation is the way to go, good to know.

I think we can do it safely, but will definitely have to apply lots of scrutiny/testing to this.

For sure first thing to do is to add some tests with skip so that in the first commits of the pull request can be used to assess that there is no sql injection possibility

[grigi]

We are not too strict on anything here. Except possibly the code. But even there I have worked with projects where there is some kind of tribal agreement as to how code should be written. As long as the CI passes and there isn't an obviously better way of doing things, it's all good. Just look at the abomination that is our test helpers :-( They are really showing their age, and possibly a reason to bump major version as it affects a lot of people.

[thisisthemurph]

Has this been implemented, I have searched the documentation, but I can't find anything there. May be my searching abilities.

[thisisthemurph]

Ok, a little more searching let me to the correct location. This seems to have been implemented as contains and icontains.

https://tortoise.github.io/query.html#filtering

[a-shaikh12]

I want to work on it plz assign it to me..

[waketzheng]

I want to work on it plz assign it to me..

@a-shaikh12 Do you still want to do that?

[waketzheng]

1. startswith/endswith/contains is more pythonic than like:

name LIKE %<string> == name__startswith=<string>
name LIKE <string>% == name__endswith=<string>
name LIKE %<string>% == name__constains=<string>
name LIKE <string1>%<string2> == name__startswith=<string1>,name__endswith=<string2>

2. you can use RawSQL to execute LIKE statement:

class Users(Model):
    name = fields.CharField(20)

await Users.annotate(_like=RawSQL("name LIKE 'o_nk%'")).filter(_like=True)

Example::

from tortoise import Model, fields, run_async
from tortoise.contrib.test import init_memory_sqlite
from tortoise.expressions import RawSQL


class Users(Model):
    name = fields.CharField(max_length=20)
    age = fields.IntField(default=0)


@init_memory_sqlite
async def main():
    names = ["lake", "like", "likes", "lakes", "leake"]
    await Users.bulk_create([Users(name=i) for i in names])
    user_names = (
        await Users.annotate(_like=RawSQL("name LIKE 'l_ke'"))
        .filter(_like=True)
        .order_by("name")
        .values_list("name", flat=True)
    )
    assert user_names == ["lake", "like"], str(user_names)

    users = (
        await Users.filter(name__startswith="l", name__endswith="ke")
        .order_by("name")
        .values_list("name", flat=True)
    )
    assert [u for u in users if len(u) == 4] == user_names, str(users)

    assert users == (
        await Users.annotate(_like=RawSQL("name LIKE 'l%ke'"))
        .filter(_like=True, age__gte=0)
        .order_by("name")
        .values_list("name", flat=True)
    )


if __name__ == "__main__":
    run_async(main())