encode · aminalaee · Nov 4, 2021 · Oct 28, 2021 · Nov 2, 2021 · Nov 2, 2021
diff --git a/docs/making_queries.md b/docs/making_queries.md
@@ -43,6 +43,8 @@ notes = await Note.objects.exclude(completed=False).all()
 
 ### .filter()
 
+#### Django-style lookup
+
 To filter instances:
 
 ```python
@@ -69,6 +71,21 @@ notes = await Note.objects.filter(text__icontains="mum").all()
 notes = await Note.objects.filter(id__in=[1, 2, 3]).all()
 ```
 
+#### SQLAlchemy filter operators
+
+The `filter` method also accepts SQLAlchemy filter operators:
+
+```python
+notes = await Note.objects.filter(Note.columns.text.contains("mum")).all()
+
+notes = await Note.objects.filter(Note.columns.id.in_([1, 2, 3])).all()
+```
+
+Here `Note.columns` refers to the columns of the underlying SQLAlchemy table.
+
+!!! note
+    Note that `Note.columns` returns SQLAlchemy table columns, whereas `Note.fields` returns `orm` fields.
+
 ### .limit()
 
 To limit number of results:

diff --git a/orm/models.py b/orm/models.py
@@ -96,6 +96,10 @@ def table(cls):
             cls._table = cls.build_table()
         return cls._table
 
+    @property
+    def columns(cls) -> sqlalchemy.sql.ColumnCollection:
+        return cls._table.columns
+
 
 class QuerySet:
     ESCAPE_CHARACTERS = ["%", "_"]
@@ -171,11 +175,27 @@ def build_select_expression(self):
 
         return expr
 
-    def filter(self, **kwargs):
-        return self._filter_query(**kwargs)
+    def filter(
+        self,
+        clause: typing.Optional[sqlalchemy.sql.expression.BinaryExpression] = None,
+        **kwargs: typing.Any,
+    ):
+        if clause is not None:
+            self.filter_clauses.append(clause)
+            return self
+        else:
+            return self._filter_query(**kwargs)
 
-    def exclude(self, **kwargs):
-        return self._filter_query(_exclude=True, **kwargs)
+    def exclude(
+        self,
+        clause: typing.Optional[sqlalchemy.sql.expression.BinaryExpression] = None,
+        **kwargs: typing.Any,
+    ):
+        if clause is not None:
+            self.filter_clauses.append(clause)
+            return self
+        else:
+            return self._filter_query(_exclude=True, **kwargs)
 
     def _filter_query(self, _exclude: bool = False, **kwargs):
         clauses = []

diff --git a/tests/test_models.py b/tests/test_models.py
@@ -309,3 +309,24 @@ async def test_model_update_or_create():
     assert created is False
     assert user.name == "Tom"
     assert user.language == "English"
+
+
+async def test_model_sqlalchemy_filter_operators():
+    user = await User.objects.create(name="George")
+
+    assert user == await User.objects.filter(User.columns.name == "George").get()
+    assert user == await User.objects.filter(User.columns.name.is_not(None)).get()
+    assert (
+        user
+        == await User.objects.filter(User.columns.name.startswith("G"))
+        .filter(User.columns.name.endswith("e"))
+        .get()
+    )
+
+    assert user == await User.objects.exclude(User.columns.name != "Jack").get()
+
+    shirt = await Product.objects.create(name="100%-Cotton", rating=3)
+    assert (
+        shirt
+        == await Product.objects.filter(Product.columns.name.contains("Cotton")).get()
+    )