How to check if string contains value? i.e. where x like "%value%"

[Goldziher]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

statement = select(Event).where(Event.user_public_id == user.public_id)
if suggested_by:
     statement.filter(Event.suggested_by.contains(suggested_by))

Description

using SQLAlchemy I would do this

if suggested_by:
     query.filter(Event.suggested_by.contains(suggested_by))

assuming I have a statement like this:

statement = select(Event).where(Event.user_public_id == user.public_id)
if suggested_by:
     statement.filter(Event.suggested_by.contains(suggested_by))

As seen above I still use the SQLAlchemy logic in the above because I couldn't figure out any way to do this with SQLModel. Is there any way to perform a where/like query with this library?

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.9.7

Additional Context

No response

[calvo-jp]

I am looking for a way to achieve this, too. In sqlalchemy, it is possible to do this with something like User.name.ilike(search), but I am using vscode with this option "python.analysis.typeCheckingMode": "basic" and it has squiggly lines on it. The work-around I have seems a bit messy. Hope something gets implemented to solve this soon. Other than that I really like sqlmodel

[terrysclaw]

Kindly try this, it work for me.

select(User).where(User.name.like(’%’+ keyword + ‘%’))

[Goldziher]

Yup, as i stated in the OP, i can reach for the underlying sqlalchemy api and do this. But this doesn't give me the same kind of type safety and editor completion as I would get otherwise.

[johnziebro]

Given this is a few months later, there may have been improvements in SQLModel. The following seems to work by importing col and explicitly wrapping the column before using contains.

select(Event).where(col(Event.suggested_by).contains(suggested_by)

[vargasjona]

Thanks, @johnziebro your solution made it really simple also with custom queries.

query = select(User).where(and_(User.role_id == role.id, or_(col(User.first_name).contains(name), col(User.last_name).contains(name)))).order_by(User.first_name)

[mrsakkaro]

@johnziebro while your solution works beautifully, would it be possible to also make it case-insensitive?

[dmtea]

It did not worked for me (I mean vscode has squiggly lines on it too). But this worked for me:

...
from sqlmodel import column
...
_contents = session.query(Content).filter(column("link").contains("something")).order_by(ordering).offset(offset).limit(limit).all()

But it would be better with "Class.column" implementation :/

[marioax]

@mrsakkaro
A bit late, but using regexp_match(value, "i") instead of contains(value) does the trick.

[oren0e]

A bit later again but because this is an ORM I would've liked to see something like the following possible:

select(MyTableModel).where("foo" in MyTableModel.column1)

@tiangolo