Filter objects by field in custom through model in ManyToMany relationship

[pylakey]

There is example below which illustrates an attempting to execute such query with ormar:

SELECT COUNT(u.id)
FROM user u
         LEFT OUTER JOIN user_city uc on u.id = uc.user_id
         LEFT OUTER JOIN city c on c.id = uc.city_id
WHERE c.name == 'Chicago'
  AND uc.visit_date == '2021-09-16';

But there is no way to filter by fields in custom through model (or maybe it is a bug)

import asyncio
from datetime import (
    date,
    timedelta,
)

import databases
import ormar
import sqlalchemy

DATABASE_URL = "sqlite:///test.db"

metadata = sqlalchemy.MetaData()
database = databases.Database(DATABASE_URL)


class BaseModel(ormar.Model):
    class Meta(ormar.ModelMeta):
        metadata = metadata
        database = database
        abstract = True


class User(BaseModel):
    class Meta:
        tablename = "user"

    id = ormar.Integer(primary_key=True, autoincrement=True)
    full_name = ormar.String(max_length=128)


class UserCity(BaseModel):
    class Meta:
        tablename = "user_city"

    id = ormar.Integer(primary_key=True, autoincrement=True)
    visit_date = ormar.Date()


class City(BaseModel):
    class Meta:
        tablename = "city"

    id = ormar.Integer(primary_key=True, autoincrement=True)
    name = ormar.String(max_length=64, unique=True)
    users = ormar.ManyToMany(
        User,
        through=UserCity,
        related_name='cities',
        through_relation_name='city_id',
        through_reverse_relation_name='user_id'
    )


async def main():
    yesterday = str(date.today() - timedelta(days=1))  # 2021-09-16
    users_count = await User.objects.filter(
        (User.cities.name == "Chicago")
        & (UserCity.visit_date == yesterday)
    ).count()
    print(f"{users_count} user(s) visited Chicago yesterday ({yesterday})")


if __name__ == '__main__':
    engine = sqlalchemy.create_engine(DATABASE_URL)
    metadata.create_all(engine)
    asyncio.run(main())

This example of code produces KeyError:

Traceback (most recent call last):
  File "test.py", line 77, in <module>
    asyncio.run(main())
  File "/opt/homebrew/Cellar/python@3.9/3.9.7/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/runners.py", line 44, in run
    return loop.run_until_complete(main)
  File "/opt/homebrew/Cellar/python@3.9/3.9.7/Frameworks/Python.framework/Versions/3.9/lib/python3.9/asyncio/base_events.py", line 642, in run_until_complete
    return future.result()
  File "test.py", line 67, in main
    users_count = await User.objects.filter(
  File ".venv/lib/python3.9/site-packages/ormar/queryset/queryset.py", line 674, in count
    expr = self.build_select_expression().alias("subquery_for_count")
  File ".venv/lib/python3.9/site-packages/ormar/queryset/queryset.py", line 276, in build_select_expression
    exp = qry.build_select_expression()
  File ".venv/lib/python3.9/site-packages/ormar/queryset/query.py", line 157, in build_select_expression
    expr = self._apply_expression_modifiers(expr)
  File ".venv/lib/python3.9/site-packages/ormar/queryset/query.py", line 228, in _apply_expression_modifiers
    expr = FilterQuery(filter_clauses=self.filter_clauses).apply(expr)
  File ".venv/lib/python3.9/site-packages/ormar/queryset/filter_query.py", line 29, in apply
    clause = self.filter_clauses[0].get_text_clause()
  File ".venv/lib/python3.9/site-packages/ormar/queryset/clause.py", line 128, in get_text_clause
    + str(sqlalchemy.sql.and_(*self._get_text_clauses()))
  File ".venv/lib/python3.9/site-packages/ormar/queryset/clause.py", line 109, in _get_text_clauses
    return [x.get_text_clause() for x in self._nested_groups] + [
  File ".venv/lib/python3.9/site-packages/ormar/queryset/clause.py", line 109, in <listcomp>
    return [x.get_text_clause() for x in self._nested_groups] + [
  File ".venv/lib/python3.9/site-packages/ormar/queryset/clause.py", line 128, in get_text_clause
    + str(sqlalchemy.sql.and_(*self._get_text_clauses()))
  File ".venv/lib/python3.9/site-packages/ormar/queryset/clause.py", line 109, in _get_text_clauses
    return [x.get_text_clause() for x in self._nested_groups] + [
  File ".venv/lib/python3.9/site-packages/ormar/queryset/clause.py", line 109, in <listcomp>
    return [x.get_text_clause() for x in self._nested_groups] + [
  File ".venv/lib/python3.9/site-packages/ormar/queryset/actions/filter_action.py", line 149, in get_text_clause
    clause = getattr(self.column, op_attr)(filter_value)
  File ".venv/lib/python3.9/site-packages/ormar/queryset/actions/query_action.py", line 63, in column
    return self.target_model.Meta.table.columns[aliased_name]
  File ".venv/lib/python3.9/site-packages/sqlalchemy/sql/base.py", line 1192, in __getitem__
    return self._index[key]
KeyError: 'visit_date'

[collerek]

There is a way (at least for django style queries, don't remember if i tested this for Python style queries, but would expect this out of myself🤣).

You just need to go from the main model with the through model field name, remember that filters need to come from the main model.

https://collerek.github.io/ormar/relations/many-to-many/#through-fields

[pylakey]

I've inspected Meta.fields and this query now works:

yesterday = str(date.today() - timedelta(days=1))  # 2021-09-16
users_count = await User.objects.filter(
    (User.cities.name == "Chicago")
    & (User.usercity.visit_date == yesterday)
).count()

But it doesn't seem obvious. Field name is 'usercity" and not "user_city" as it set in Meta.tablename. What logic of such fields naming implemented in ormar now? Just lowercasing class name?

[collerek]

Yes, by default it's just class name to lower.
Need to add this to documentation, thanks for catching that!