sqlmodel-filters 0.1.0

A Lucene query like filtering for SQLModel

sqlmodel-filters

A Lucene query like filter for SQLModel.

Installation

pip install sqlmodel-filters

How to Use

Let's say we have the following model & records:

import datetime

from sqlmodel import Field, Relationship, Session, SQLModel, create_engine


class Headquarter(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)

    teams: list["Team"] = Relationship(back_populates="headquarter")


class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)

    headquarter_id: int | None = Field(default=None, foreign_key="headquarter.id")
    headquarter: Headquarter | None = Relationship(back_populates="teams")

    heroes: list["Hero"] = Relationship(back_populates="team")


class Hero(SQLModel, table=True):  # type: ignore
    id: int | None = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: int | None = None
    created_at: datetime.datetime = Field(default_factory=datetime.datetime.utcnow)

    team_id: int | None = Field(default=None, foreign_key="team.id")
    team: Team | None = Relationship(back_populates="heroes")


headquarter_1 = Headquarter(id=1, name="Sharp Tower")
headquarter_2 = Headquarter(id=2, name="Sister Margaret's Bar")


team_1 = Team(id=1, name="Preventers", headquarter_id=1)
team_2 = Team(id=2, name="Z-Force", headquarter_id=2)


hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)

engine = create_engine("sqlite://")


SQLModel.metadata.create_all(engine)


with Session(engine) as session:
    for obj in [headquarter_1, headquarter_2, team_1, team_2, hero_1, hero_2, hero_3]:
        session.add(obj)

    session.commit()

And let's try querying with this library.

# this library relies on luqum (https://github.com/jurismarches/luqum) for parsing Lucene query
from luqum.thread import parse
from sqlmodel import Session

from sqlmodel_filters import SelectBuilder

# parse a Lucene query
parsed = parse('name:Spider')
# build SELECT statement for Hero based on the parsed query
builder = SelectBuilder(Hero)
statement = builder(parsed)
# the following is a compiled SQL query
statement.compile(compile_kwargs={"literal_binds": True})

The compiled SQL query is:

SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.created_at
FROM hero
WHERE hero.name = '%Spider%'

And you can execute the query to get Hero objects.

>>> heros = session.exec(statement).all()
[Hero(name='Spider-Boy', id=2, team_id=1, age=None, secret_name='Pedro Parqueador', created_at=datetime.datetime(...))]

Specs

Type Casting

A value is automatically casted based on a field of a model.

Query	SQL (Where Clause)	Field
age:48	WHERE hero.age = 48	age: Optional[int]
created_at:2020-01-01	WHERE hero.created_at = '2020-01-01 00:00:00'	created_at: datetime.datetime

Word (Term)

• Double quote a value if you want to use the equal operator.
• The LIKE operator is used when you don't double quote a value.
• Use ? (a single character wildcard) or * (a multiple character wildcard) to control a LIKE operator pattern.
• * is converted as IS NOT NULL.

Query	SQL (Where Clause)
name:Spider-Boy"	WHERE hero.name = 'Spider-Boy'
name:Spider	WHERE hero.name LIKE '%Spider%'
name:Deadpond?	WHERE hero.name LIKE 'Deadpond_'
name:o*	WHERE hero.name LIKE 'o%'
name:*	WHERE hero.name IS NOT NULL

REGEX

Query	SQL (Where Clause)
name:/Spider?Boy/	WHERE hero.name <regexp> 'Spider?Boy'

[!NOTE] Regex support works differently per backend. See SQLAlchemy docs for details.

FROM & TO

Query	SQL (Where Clause)
age:>40	WHERE hero.age > 40
age:>=40	WHERE hero.age >= 40
age:<40	WHERE hero.age < 40
age:<=40	WHERE hero.age <= 40

RANGE

Query	SQL (Where Clause)
age:{48 TO 60}	WHERE hero.age < 60 AND hero.age > 48
age:[48 TO 60]	WHERE hero.age <= 60 AND hero.age => 48

AND, OR, NOT and GROUP (Grouping)

Query	SQL (Where Clause)
name:Rusty AND age:48	WHERE hero.name LIKE '%Rusty%' AND hero.age = 48
name:Rusty OR age:47	WHERE hero.name LIKE '%Rusty%' OR hero.age = 47
NOT name:Rusty	WHERE hero.name NOT LIKE '%Rusty%'
(name:Spider OR age:48) AND name:Rusty	WHERE (hero.name LIKE '%Spider%' OR hero.age = 48) AND hero.name LIKE '%Rusty%'

Note that the default conjunction is OR.

Query	SQL (Where Clause)
name:Rusty age:48	WHERE hero.name LIKE '%Rusty%' OR hero.age = 48

Field Grouping

Lucene's field grouping syntax field:(A OR B) applies the outer field name to each term inside the group:

Query	SQL (Where Clause)
name:(Spider OR Rusty)	WHERE hero.name LIKE '%Spider%' OR hero.name LIKE '%Rusty%'
name:(Spider AND Boy)	WHERE hero.name LIKE '%Spider%' AND hero.name LIKE '%Boy%'
name:(Spider Rusty)	WHERE hero.name LIKE '%Spider%' OR hero.name LIKE '%Rusty%'
name:(NOT Spider)	WHERE hero.name NOT LIKE '%Spider%'
age:([47 TO 50] OR >100)	WHERE (hero.age <= 50 AND hero.age >= 47) OR hero.age > 100

A SearchField nested inside a field group overrides the outer field name (matching Lucene's documented semantics), so name:(Spider OR age:48) compiles to WHERE hero.name LIKE '%Spider%' OR hero.age = 48.

Relationship

Set relationships (key-to-model mapping) to do filtering on relationship(s).

>>> parsed = parse('name:Spider AND team.name:"Preventers" AND team.headquarter.name:Sharp')
>>> builder = SelectBuilder(Hero, relationships={"team": Team, "headquarter": Headquarter})
>>> statement = builder(parsed)
>>> statement.compile(compile_kwargs={"literal_binds": True})
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.created_at, hero.team_id
FROM hero JOIN team ON team.id = hero.team_id JOIN headquarter ON headquarter.id = team.headquarter_id
WHERE hero.name LIKE '%Spider%' AND team.name = 'Preventers' AND headquarter.name LIKE '%Sharp%'

Many-to-Many Relationship

If you have a many-to-many relationship like the following:

class Tag(SQLModel, table=True):
    __tablename__ = "tags"  # type: ignore

    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(...)

    posts: list["Post"] = Relationship(back_populates="tags", sa_relationship_kwargs={"secondary": "taggings"})


class Post(SQLModel, table=True):
    __tablename__ = "posts"  # type: ignore

    id: int | None = Field(default=None, primary_key=True)

    tags: list["Tag"] = Relationship(back_populates="posts", sa_relationship_kwargs={"secondary": "taggings"})


class Tagging(SQLModel, table=True):
    __tablename__ = "taggings"  # type: ignore

    tag_id: str = Field(foreign_key="tags.id", primary_key=True)
    post_id: str = Field(foreign_key="posts.id", primary_key=True)

A value of the relationships keyword argument should be a dict which has:

• join: a many-to-many relationship to join.
• isouter: whether to generate LEFT OUTER join or not. Defaults to False.
• full: whether to generate FULL OUTER join or not. Defaults to False.
• model: a model of a many-to-many relationship.

For example:

>>> tree = parse("tags.name:foo")
>>> builder = SelectBuilder(Post, relationships={"tags": {"join": Post.tags, "model": Tag}})  # type: ignore
>>> statement = builder(tree)
>>> statement.compile(compile_kwargs={"literal_binds": True})
SELECT posts.id
FROM posts JOIN taggings AS taggings_1 ON posts.id = taggings_1.post_id JOIN tags ON tags.id = taggings_1.tag_id
WHERE tags.name LIKE '%foo%'

Entity

Set entities to select specific columns.

>>> tree = parse("name:*")
>>> statement = builder(tree, entities=(Hero.id, Hero.name))
>>> session.exec(statement).all()
[(1, "Deadpond"), (2, "Spider-Boy"), (3, "Rusty-Man")]

You can also use a function like count.

>>> tree = parse("name:*")
>>> statement = builder(tree, entities=func.count(Hero.id))
>>> session.scalar(statement)
3

Default Fields

Default fields, all of the fields in a model by default, are used when you don't set a field in a query.

Query	SQL (Where Clause)
Spider	WHERE hero.name LIKE '%Spider%' OR hero.secret_name LIKE '%Spider%'

You can override the default by setting default_fields.

builder = SelectBuilder(Hero, default_fields={"name": Hero.model_fields["name"]})

Helper Function

q_to_select function parses a query and builds a select statement on the fly.

from sqlmodel_filters import q_to_select

statement = q_to_select('name:"Spider-Boy"', Hero)