Usage

The entirety of sqlalchemy-querybuilder’s functionality is encapsulated in the Filter class.

Getting started

Filter class accepts three parameters:

• models (required) can be either a module defining classes which inherit from declarative_base or a dictionary of such classes with the name of the tables as keys.
• query (required) is a SQLAlchemy query object. Optionaly loaded with some entity.
• operators (optional) is a dictionary of operators. By default all operators are enabled. This is how they are all implemented:
  • equal → __eq__(x)
  • not_equal → __ne__(x)
  • less → __lt__(x)
  • greater → __gt__(x)
  • less_or_equal → __le__(x)
  • greater_or_equal → __ge__(x)
  • in → in_(x)
  • not_in → notin_(x)
  • ends_with → like('%' + x)
  • begins_with → like(x + '%')
  • contains → like('%' + x + '%')
  • not_contains → notlike('%' + x + '%')
  • not_begins_with → notlike(x + '%')
  • not_ends_with → notlike('%' + x)
  • is_empty → __eq__('')
  • is_not_empty → __ne__('')
  • is_null → is_(None)
  • is_not_null → isnot(None)
  • between → between(x[0], x[1])

Filter only has one method: querybuilder. This method only accepts rules parameter, which is a dictionary that holds rules processed by jquery QueryBuilder or compatible.

Examples

Given the file models.py containing the following mappings:

from sqlalchemy import Column, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyModel(Base):
    __tablename__ = 'test1'
    parentid = Column('test1id', Integer, primary_key=True)

You could use sqlalchemy-querybuild like:

Using a module

from sqlalchemy_querybuilder import Filter
import models

rule = {
        "condition": "OR",
        "rules": [{
                    "id": "price",
                    "field": "test1.parentid",
                    "type": "integer",
                    "input": "number",
                    "operator": "equal",
                    "value": "1"
                   },
                  ],
}
filter = Filter(models, session.query())
print(filter.querybuilder(rule)

Using a dictionary instead of a module

from sqlalchemy.orm import Session
from sqlalchemy_querybuilder import Filter
import models

rule = {
        "condition": "OR",
        "rules": [{
                    "id": "price",
                    "field": "test1.parentid",
                    "type": "integer",
                    "input": "number",
                    "operator": "equal",
                    "value": "1"
                   },
                  ],
}
filter = Filter({"test1": models.MyModel}, session.query())
print(filter.querybuilder(rule)

Using a previously loaded query

from sqlalchemy_querybuilder import Filter
import models
import other

rule = {
        "condition": "OR",
        "rules": [{
                    "id": "price",
                    "field": "test1.parentid",
                    "type": "integer",
                    "input": "number",
                    "operator": "equal",
                    "value": "1"
                   },
                  ],
}
filter = Filter(models, session.query(other.MyOtherModel))
print(filter.querybuilder(rule)