Hola Julio, primero que nada muchas gracias por el package, estoy creando una Api Rest con flask y me está siendo super util.
Te queria consultar un problema que estoy teniendo al filtrar un modelo relacionado a otro modelo, es una relación de uno a muchos que tengo entre pacientes y consultas, y quiero obtener, por ejemplo, un paciente con todas sus consultas menos una (que seria la del id 11) pero no está funcionando, solo funciona el filtro en el modelo Paciente:

def get_paciente_filtering_consultas(id):
    query = db.session.query(Paciente)
    filter_spec = [
        {'model': 'Paciente', 'field': 'id', 'op': '==', 'value': id},
        {'model': 'PacienteConsulta', 'field': 'id', 'op': '!=', 'value': 11},
    ]
    filtered_query = apply_filters(query, filter_spec)
    return filtered_query.first()

la relación en el modelo Paciente está definida de la siguiente forma:

class Paciente(db.Model):
    """ Paciente Model """
    __tablename__ = "paciente"
    consultas = db.relationship('PacienteConsulta', lazy='joined',order_by="desc(PacienteConsulta.created_at)")

El filtro del id es solo de prueba, en realidad mi idea es filtrar consultas por fecha, pero al probar esto me di cuenta que tampoco funcionaba.

Muchas gracias!

I have encounterred following errors using
SQLAlchemy 1.4.0b1

query = apply_filters(query, sqla_filters)
File "/opt/venv/lib/python3.8/site-packages/sqlalchemy_filters/filters.py", line 235, in apply_filters
default_model = get_default_model(query)
File "/opt/venv/lib/python3.8/site-packages/sqlalchemy_filters/models.py", line 141, in get_default_model
query_models = get_query_models(query).values()
File "/opt/venv/lib/python3.8/site-packages/sqlalchemy_filters/models.py", line 64, in get_query_models
models.extend(mapper.class_ for mapper in query._join_entities)
AttributeError: 'Query' object has no attribute '_join_entities'

Hi all,

I am just wondering if the functionality for filtering with comparisons between fields is available through "apply_filters"?

e.g. if field_1 > field_2

I have tried { "model" : "Foo", "field": "field_1", "op":">", "field": "field_2" } however the filter requires a "value".

SQLAlchemy recommends a new way of writing ORM queries, using select(): https://docs.sqlalchemy.org/en/14/changelog/migration_20.html
Could you update this library to support it?

Currently, it triggers errors such as: 'Select' object has no attribute '_compile_state'

I've got a related model that provides a key/value extension of the main model and I'd like to be able to sort on the related model key/value. So, I want to say, "sort on the value field, if the key matches foo". I thought maybe I could use a hybrid method and pass the key to the method as shown below. I don't see anyway to pass that value to the hybrid method. I get the way the API is made, is there another way to accomplish this? If not, is can we add maybe an optional kwargs to the sort_spec that gets expanded if the sort field has the __call__ attribute? Another approach is maybe a sort of subquery?

class ImageProperty(db.Model):
    __tablename__ = "image_properties"

    id: Mapped[int] = mapped_column(primary_key=True)
    image_id: Mapped[str] = mapped_column(ForeignKey("images.id"))
    key: Mapped[str]
    value: Mapped[str]

    def __init__(self, key: str, value: str):
        self.key = key
        self.value = value

    @hybrid_method
    def value_for_key(self, key: str) -> str | None:
        if self.key == key:
            return self.value
        else:
            return None

class Image(ProxiedDictMixin, db.Model):
    __tablename__ = "images"

    id: Mapped[str] = db.Column(db.String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    properties: Mapped[dict[str, "ImageProperty"]] = relationship(
        collection_class=attribute_keyed_dict("key"), cascade="all, delete-orphan"
    )

Snippet to sort

if hasattr(Image, key):
    logger.debug("%s", {"model": "Image", "field": key, "direction": dir})
    sort_spec.append({"model": "Image", "field": key, "direction": dir})
else:
    logger.debug("%s", {"model": "ImageProperty", "field": key, "direction": dir})
    # TODO(dcode): This isn't right. need to sort on value when key == key
    sort_spec.append({"model": "ImageProperty", "field": value_for_key, "direction": dir})

in pagination.py:

def apply_pagination(...)
...

return query, Pagination(page_number, page_size, num_pages, total_results)

in the document:

page_size, page_number, num_pages, total_results = pagination

it will cause page_size became page_number and page_number became page_size

Hi,
I am trying to use this project on python2 and I get the following error,

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/lib/python2.7/site-packages/sqlalchemy_filters/__init__.py", line 3, in <module>
    from .filters import apply_filters  # noqa: F401
  File "/usr/lib/python2.7/site-packages/sqlalchemy_filters/filters.py", line 3, in <module>
    from inspect import signature
ImportError: cannot import name signature

Are there any plans to update this package to work with sqlalchemy 1.4.0?

Hello,
I am in the process of evaluating this library for use in one of the projects we are working on. I see the last commit was over 9 months ago. Is this library being actively maintained? If so when is the next release?
Thank you
Sameer

Is there a way to do groupby operations?

I get this message:
TypeError: 'SortFilterQuery' object is not callable

When using the Sorting function and query contains a column generated using the count column.
Error is in below function at models.get_query_models.
`
def get_query_models(query):
"""Get models from query.

:param query:
    A :class:`sqlalchemy.orm.Query` instance.

:returns:
    A dictionary with all the models included in the query.
"""
models = [col_desc['entity'] for col_desc in query.column_descriptions]

`

Here for such column models contain a None element.

I am proposing a solution where we can remove the None value from the list.

Hello, please could we have a mechanism for defining an escape character for a search string, or alternatively have an escape chaacter such as \ predefined?

Many thanks

Hi, is it possible to add new Operator to support JSON_CONTAINS

'json_contains': lambda f, a: func.json_contains(
            f, func.json_array(a)
        ),

Thanks!

Are you planning to support SQLAlchemy 2.X in the near future?

I got above error when applying filtering after join.
Here is how I join
query_obj = db_session.query(ModelA).outerjoin(ModelB).filter(ModelB.some_col == some_val)

Sqlalchemy allows hybrid-properties on a model that can generate sql expressions, and be used in queries etc.
https://docs.sqlalchemy.org/en/latest/orm/extensions/hybrid.html

Currently, a sqlalchemy_filters.exceptions.FieldNotFound error is raised if we try to use them in a sqlalchemy-filters filter.

I have an issue, where my datamodel, sometimes have a relation to another table, and sometimes it is None. I use sqlalchemy-filters to search for items in a table, however when I add filter_or for a relation where the model name doesn't exists, all the items are excluded.

Is there any way I can avoid this behaviour? When i include some columns that only exists for some items/rows, even if try to search for another column, the items disappear because the column is included in the filter_or

In the below example, ProjectReference only exists for som items, where as other items have a relation to another table

filter_or=[
{"model": "ProjectReference", "field": "project_reference_name", "op": "ilike", "value": "%12345%"},
{"model": "SupplierPart", "field": "supplier_part_description", "op": "ilike", "value": "%12345%"},
{"model": "SupplierPart", "field": "part_inwatec_id", "op": "ilike", "value": "%12345%"},
{"model": "SupplierPart", "field": "supplier_part_id", "op": "ilike", "value": "%12345%"},
{"model": "Supplier", "field": "name", "op": "ilike", "value": "%12345%"}]

Is it possible to support the filtering function by the key values of JSON fields?
SQLAlchemy has a JSON_EXTRACT function and I can filter Model.json_column ['key'] == value

Hi,

I found something interesting. My db model has a few foreign relationships ... and I found that in the crud operation I need to "call" them, otherwise they're not included ... any ideas why this would be?

model:
`class Invoice(Base):

__tablename__ = "invoice"

id = Column(Integer, primary_key=True)
net_total = Column(Float(), nullable=False, server_default="0")
tax = Column(Float(), nullable=False, server_default="0")
total = Column(Float(), nullable=False, server_default="0")
invoice_num = Column(Integer)
year = Column(Integer, nullable=False, server_default=str(now.year))
# STATUSES: 1:open, 2:paid
status = Column(Integer, nullable=False, server_default="1")
invoice_date = Column(Date(), server_default=str(now))
invoice_due = Column(Date(), server_default=str(now))
service_rendered = Column(Date(), server_default=str(now))
created = Column(DateTime(), server_default=str(now))
updated = Column(DateTime(), server_onupdate=str(now))
description = Column(Unicode(255), server_default=f"Zaračunavamo vam ... ")

# foreign keys & relationships
company_id = Column(Integer(), ForeignKey("company.id"), nullable=False)
company = relationship("Company", back_populates="invoices")
partner_id = Column(Integer(), ForeignKey("partner.id"), nullable=False)
partner = relationship("Partner", back_populates="invoices")
items = relationship("InvoiceItems", back_populates="invoice")`

crud:

`
def get_invoices(db: Session, page: int = 1, limit: int = 100):

query = db.query(models.Invoice)

filter_spec = [{'field': 'status', 'op': '==', 'value': 2}]

filtered_query = apply_filters(query, filter_spec)

sort_spec = [
    {'model': 'Invoice', 'field': 'total', 'direction': 'desc'},
]

sorted_query = apply_sort(query, sort_spec)

paginated_query, pagination = apply_pagination(
    sorted_query, page_number=1, page_size=3)

query_result = paginated_query.all()

# for whatever magic you need to call children to be included?
for item in query_result:
    # just call them ...
    company = item.company
    partner = item.partner
    items = item.items
    # ... and do nothing.
    pass

result = {"rows": pagination.total_results,
          "pages": pagination.num_pages, "items": query_result}

return result`

Does anyone know why this error comes out?

Hi,

I've been successfully using this excellent tool for a few months and this is the very first time I find myself in need of using datetime-based filters.

I'm trying to apply a range but the resulting query doesn't seem to be what I was expecting.

The filter itself looks like:
[{'model': 'Job', 'field': 'dueDate', 'operator': 'gt', 'value': datetime.datetime(2008, 6, 1, 0, 0)}]

Which is later on rendered as:

WHERE job."dueDate" = %(dueDate_1)s

So, the filter is somewhat applied, but with the wrong operator.

I'm open to provide extended data if required.

Thanks a lot for your help.

Cheers,
Javier

Hello,

In my application I really need an apply_filters OR edition
I have made a custom function for this, but would there be an interest in a PR to include this in the regular apply_filters? backwards compatible by an optional argument called "as_or" or similar?

Please add the sqlachemy operator "contains" to the OPERATORS dictionary:
'contains': lambda f, a: f.contains(a),

Thank you!

DeprecationWarning: Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated, and in 3.8 it will stop working from collections import Iterable, namedtuple in filters.py

Hi @juliotrigo,

There has been some time since the sqlalchemy 1.4 release and the initial effort to bring the support for new version into this project. so far without response. Could you spend a few minutes to elaborate on your ability and willingness to continue a work on maintaining the project or sharing responsibilities for it's maintenance please ?

to all others using this project:

since I'm not a true python developer, I'm a little bit unsure about if me, or someone else might be willing to fork and continue to support this project. Also, according to the CI in github actions, there has been already some breaking changes in EOLed python3.5 code which magically prevents sqlalchemy to work properly so I wonder, if fork will take place should we try to keep so much backward compatibility or we'll be happy with just python >= 3.6 and sqlalchemy >= 1.3 ?

any ideas are welcome

When attempting to filter on the id of a model with UUID as the type, I receive the error:

psycopg2.errors.UndefinedFunction: operator does not exist: uuid ~~* unknown
LINE 3: ...a-556b4fc5b33c'::uuid AND my_table.id ILIKE '123...

Could we, perhaps, supply a cast value in the filter entry, or, even better, dynamically detect the type of the column and provide the coercion, as done in SqlAlchemy Coercion

Providing an explicit type cast as:

filters = [{'field': 'id', 'op': 'ilike', 'value': '123%', 'cast': 'uuid'}]

...to generate the SQL:

WHERE my_table.id ILIKE '123%'::uuid

Hello!

I'm currently using this library but it seems like I'm not able to use operators gt and eq on sqlalchemy's Time column type.

I guess it is comparing using the value string representation of the information.. am I right? Is there a way to override the comparator for a particular column or at least override it globally?

Thank you

Will this library still make use of SQLAlchemy anti-sql injection features or does it break it?

https://stackoverflow.com/a/16001395

Currently there is no type validation.

If the following query is ran against the example models SQLAlchemy will raise a StatementError exception.

{'field': 'count', 'op': '==', 'value': 'not an int'}

class Base(object):
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    count = Column(Integer, nullable=True)


Base = declarative_base(cls=Base)


class Foo(Base):

    __tablename__ = 'foo'

It would be useful to validate the value against the model's field type before the query is executed. This would allow the proper HTTP error to be given to a REST API user.

It probably would make sense to use the same conversion mechanism that is already used in SQLAlchemy so custom types can be validated as well.