Problem Description: Till 0.6.0 (inclusive) we could pass a sqlalchemy.orm.Query object to odata_query.sqlalchemy.apply_odata_query and it worked. With 0.7.0 this changed and apply_odata_query breaks with the following exception:

AttributeError: 'Query' object has no attribute 'columns_clause_froms'

Example code to reproduce:

from odata_query.sqlalchemy import apply_odata_query

orm_query = session.query(MyModel)  # This is a SQLAlchemy Query object
odata_query = "name eq 'test'"  # This will usually come from a query string parameter.

query = apply_odata_query(orm_query, odata_query) # this breaks
results = query.all()

Notes: The title deliberately contains the word "implicit" as apply_odata_query's query param has type hint sqlalchemy.sql.expression.ClauseElement and sqlalchemy.orm.Query does not inherit from it.

So this issue could also be seen as the question whether it is intended to support SQLAlchemy's Query-API in general as it is kind of deprecated but I guess a lot of folks out there are still using it.

I'm using the raw lexer (no provided mongodb or other integrations).

Let's look at this example:

from odata_query.grammar import ODataLexer, ODataParser
lexer = ODataLexer()
parser = ODataParser()
odata_filter = parser.parse(lexer.tokenize("foo eq 15"))

I would expect that odata_filter contains a numerical value for foo, instead I get:

Compare(comparator=Eq(), left=Identifier(name='foo'), right=Integer(val='15'))

Hello! Thank you for this wonderful contribution to the community! We have been using odata-query with our Django / DRF project to provide OData representations of our 1st-party objects in Salesforce via External Objects, but as we are expanding the usage of OData, we have encountered some difficulty with the parser. For example, when exposing one of our Django models via a filterable OData API for Salesforce, we encountered this error:

TypeException

Cannot apply 'Eq' to 'Call(func=Identifier(name='contains'), args=[Identifier(name='category'), String(val='danielle hutchens')])'

From odata_query/django/django_q.py in visit_Compare at line 194:

node: Compare(comparator=Eq(), left=Call(func=Identifier(name='contains'), args=[Identifier(name='category'), String(val='danielle hutchens')]), right=Boolean(val='true')) . . .

From odata_query/django/shorthand.py in apply_odata_query at line 24:

odata_query:

"contains(category,'danielle hutchens') eq true or contains(email,'danielle hutchens') eq true or contains(filename,'danielle hutchens') eq true or contains(file_id,'danielle hutchens') eq true or contains(path,'danielle hutchens') eq true or contains(revision,'danielle hutchens') eq true or contains(content_hash,'danielle hutchens') eq true or contains(subcategory,'danielle hutchens') eq true or contains(hyker__sfaccountid,'danielle hutchens') eq true or contains(hyker__sfopportunityid,'danielle hutchen...

Can you confirm whether odata-query supports these kinds of comparisons? Are we (or Salesforce External Objects) doing something wrong?

Hello again!

May I suggest you add your nice contributor's guide to the read the docs? This can be done without duplicating the documentation's source:

1. Convert (manually) CONTRIBUTING.md to RestructuredText as CONTRIBUTING.rst (Github will still render it just fine as HTML).

2. Add a contributing.rst file to your doc and add it somewhere to the doctree with the following content:

.. _contributing:

.. include:: ../CONTRIBUTING.rst

3. There is no third step!

Hi there,

Thank you for making this lovely library.

I currently use the AstToSqlAlchemyClauseVisitor which works well with SQLAlchemy ORM, but my application is async so I use sqlalchemy core everywhere apart from where OData is involved. My database does not support async driver so sqlalchemy 2.0 async support won't actually bring an difference to me. With sqlalchemy core I build the query statements and handle all aspects around execution of the statement myself with loop.run_in_executor, which again does not work well with ORM.

Would it possible to add support for sqlalchemy core?

Hi, I am starting using odata-query for a project (Thanks!) and I noticed that the links in the docs are broken Is it OK if I make a PR with the updated links? So far, I have noticed just a couple of places:

• PyPi page "contributing guidelines"
• readthedocs "Reporting a bug" link to issues

Right now, it's not possible to check the code which uses the library:

some_file:15: error: Skipping analyzing "odata_query": module is installed, but missing library stubs or py.typed marker
some_file:15: note: See https://mypy.readthedocs.io/en/stable/running_mypy.html#missing-imports
some_file: error: Skipping analyzing "odata_query.grammar": module is installed, but missing library stubs or py.typed marker
some_file: error: Skipping analyzing "odata_query.exceptions": module is installed, but missing library stubs or py.typed marker
some_file: error: Skipping analyzing "odata_query.typing": module is installed, but missing library stubs or py.typed marker
some_file: error: Skipping analyzing "odata_query.visitor": module is installed, but missing library stubs or py.typed marker

It would be great to get this ability.

PS: I've implemented my custom visitor, but I'm not sure if it's worth sharing; it's unique to our requirements.

When the lexer encounters an unknown token, it'll raise a NameError instead of the intended TokenizingException because of this typo.

self = <odata_query.grammar.ODataLexer object at 0x7f7dca170e10>, token = Token(type='ERROR', value="'ROUNDHOUSE", lineno=1, index=15)

    def error(self, token):
        """
        Error handler during tokenization
    
        Args:
            token: The token that failed to tokenize.
        Raises:
            TokenizingException
        """
>       raise exceptions.TokenizingException(text)
E       NameError: name 'text' is not defined

../deps/venv/lib/python3.7/site-packages/odata_query/grammar.py:108: NameError

While packaging your library for Fedora (see https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=2031000), I've noticed that Faker and moto from testing extra are not used. Please consider removing them from the requirements.

Datetime extracts currently use the wrong parameter order. See: https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/_elements_constructors.py#L1007

I've upgraded pytest to version 7, since it's a good thing by itself, but also because I've received the report from Fedora people (they want to push pytest version 7, which is not compatible with the library): https://bugzilla.redhat.com/show_bug.cgi?id=2059977.

Basic support for SQLAlchemy Core was added in v0.7.0, but this does not include relationship traversal (e.g. author/blogposts) or collection lambda blogposts/any(b: b/title eq 'test') functionality.

To implement this we need a way to find the related table and its target column. In the ORM visitor, this is easy because mapped models usually include a relationship attribute with all necessary details. Core Table objects seem to lack such a linking property.

All integration tests for this are already present, but currently marked xfail.

The AST to SQL transformer still contains some Athena-specific code. Ideally this library contains a pure, generic SQL transformer, and an Athena/Presto dialect can be maintained in a seperate library/extension.

Hello,

thank you for this phantastic library, it helps me a lot!

However, I experienced that filtering by uuid columns is not working. Here a minimal example:

import uuid

import sqlalchemy as sa
from sqlalchemy.orm import declarative_base
from sqlalchemy.orm.session import Session
from sqlalchemy_utils import UUIDType
from odata_query.sqlalchemy import apply_odata_query

Base = declarative_base()

class MyTable(Base):
    __tablename__ = "mytable"
    id = sa.Column(UUIDType(binary=False), primary_key=True, default=uuid.uuid4, nullable=False)

DATABASE_URL = "sqlite:///test.db"
engine = sa.create_engine(DATABASE_URL)

Base.metadata.drop_all(bind=engine)
Base.metadata.create_all(bind=engine)

with Session(engine) as db:
    t = MyTable()
    db.add(t)
    db.commit()

    q = db.query(MyTable)
    q = apply_odata_query(q, f"id eq {t.id}")
    print(q.all())

Output: [] The UUIDType(binary=False) generates CHAR(32).

I found out that the following change to ./odata-query/sqlalchemy/sqlalchemy_clause.py makes it work (is the missing 'py_' a typo?):

def visit_GUID(self, node: ast.GUID) -> BindParameter:
        ":meta private:"
        # return literal(node.val)    # Old version
        return literal(node.py_val)  # New version

Output: [<__main__.MyTable object at 0x000001F8295C3DC0>]

Also it seems like the tests in ./odata-query/tests/integration/sqlalchemy/test_querying.py do imply filtering with uuids but only against non-matching:

(Author, "id eq a7af27e6-f5a0-11e9-9649-0a252986adba", 0),
(Author, "id in (a7af27e6-f5a0-11e9-9649-0a252986adba, 800c56e4-354d-11eb-be38-3af9d323e83c)", 0),

Would be nice if this could be fixed.

*Edit: Just found out that my proposed change leads to exceptions when using the in clause, that are otherwise not present:

...
q = apply_odata_query(q, f"id in ({t.id}, 800c56e4-354d-11eb-be38-3af9d323e83c)")
...

Outputs:

sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type.
[SQL: SELECT mytable.id AS mytable_id
FROM mytable
WHERE mytable.id IN (?, ?)]
[parameters: (UUID('0fc434f4-d10d-4725-8d4f-4e00972dea15'), UUID('800c56e4-354d-11eb-be38-3af9d323e83c'))]
(Background on this error at: https://sqlalche.me/e/14/rvf5)

Hi, I faced a problem that very likely has a simple solution that I do not see. I am using release v0.5.2 to modify sqlalchemy queries.

I tried to use the module to filter a query that selects more than one ORM entity. I faced the problem that AstToSqlAlchemyClauseVisitor expects a single type on entity, but the query gets and filter fields from ORM entities of different types. I guess there is a way of doing this and I do not realize which is it.

As an example, using the models from models.py used in testing, I did not find a proper way to freely filter the following query:

stmt = select(Author, Comment).join(Comment)

Using a similar approach as in shorthand.apply_odata_query I can apply filters on fields of any of the two entities (using AliasRewriter if needed), but since AstToSqlAlchemyClauseVisitor accepts a single entity I do not see a way to filter on both.

Please let me know if there is a proper way to manage this kind of queries. Meanwhile, I made an ugly hack to circumvent this, that it probably breaks many things.

SQLAlchemy offers great support for ENUM fields, mapping them from either a list of options or a Python Enum. OData-Query doesn't utilize this feature well enough, and currently just crashes if you try to use a non-existing ENUM member.