Repository: tortoise/tortoise-orm

Issue:
QuerySet build sql error When multi ForeignKeyField ref to one model
**Describe the bug**
```python
class User(Model):
    username = CharField(max_length=20, unique=True, description='用户名')
    nickname = CharField(description='昵称', max_length=20)
    email = CharField(description='EMail', max_length=50, null=True)
    mobile_tel = CharField(description='手机', max_length=15, null=True)

class OperationLog(Model):
    added_quantity = IntField(description='新增数量')
    changed_quantity = IntField(description='更新数量')
    operated_at = DateTimeField(auto_now_add=True, description='更新时间')
    operated_by: ForeignKeyRelation[User] = ForeignKeyField('models.User',
                                                            description='更新人')

class History(Model):
    num = CharField(max_length=14, description='设备编码')
    log: ForeignKeyRelation[OperationLog] = ForeignKeyField(
        'models.OperationLog',
        related_name='created_histories',
        description='操作记录')
   changed_log: ForeignKeyRelation[OperationLog] = ForeignKeyField(
        'models.OperationLog',
        related_name='changed_histories',
        description='变更操作')

```

```python

qset = History.all().values('id', 'num', 'log__operated_at', 'log__operated_by__nickname', 'changed_log__operated_at', 'changed_log__operated_by__nickname')

query = await qset

qset.query.get_sql()

```
> SELECT "history"."id" "id","history"."num" "num","operationlog"."operated_at" "log__operated_at","user"."nickname" "log__operated_by__nickname","operationlog"."operated_at" "changed_log__operated_at","user"."nickname" "changed_log__operated_by__nickname" FROM "history" LEFT OUTER JOIN "operationlog" ON "operationlog"."id"="history"."log_id" LEFT OUTER JOIN "user" ON "user"."id"="operationlog"."operated_by_id"

**The generated SQL statement is obviously wrong !**

**Correct SQL statement**:
```sql
SELECT
  "a"."id" "id",
  "a"."num" "num",
  "b"."operated_at" "log__operated_at",
  "c"."nickname" "log__operated_by__nickname",
  "d"."operated_at" "changed_log__operated_at",
  "e"."nickname" "changed_log__operated_by__nickname" 
FROM "history" as a
LEFT OUTER JOIN "operationlog" as b ON b."id"=a."log_id" 
LEFT OUTER JOIN "user" as c ON "c"."id"="b"."operated_by_id"
LEFT OUTER JOIN "operationlog" as d ON d."id"=a."changed_log_id" 
LEFT OUTER JOIN "user" as e ON "e"."id"="d"."operated_by_id";
```


Discussion:
coveralls: ## Pull Request Test Coverage Report for [Build 1140](https://coveralls.io/builds/28190913)

* **40** of **40**   **(100.0%)**  changed or added relevant lines in **3** files are covered.
* No unchanged relevant lines lost coverage.
* Overall coverage increased (+**0.02%**) to **98.143%**

---



|  Totals | [![Coverage Status](https://coveralls.io/builds/28190913/badge)](https://coveralls.io/builds/28190913) |
| :-- | --: |
| Change from base [Build 1128](https://coveralls.io/builds/28180521): |  0.02% |
| Covered Lines: | 3361 |
| Relevant Lines: | 3394 |

---
##### 💛  - [Coveralls](https://coveralls.io)


Patch:
diff --git a/CHANGELOG.rst b/CHANGELOG.rst
index 0a78ccc..43b3a6b 100644
--- a/CHANGELOG.rst
+++ b/CHANGELOG.rst
@@ -2,6 +2,11 @@
 
 Changelog
 =========
+0.15.9
+------
+- Alias Foreign Key joins as we can have both self-referencing and duplicate joins to the same table.
+  This generates SQL that differentiates between which instance of the table to work with.
+
 0.15.8
 ------
 - ``TextField`` now recommends usage of ``CharField`` if wanting unique indexing instead of just saying "indexing not supported"
diff --git a/tortoise/backends/base/executor.py b/tortoise/backends/base/executor.py
index 5165aa8..7568309 100644
--- a/tortoise/backends/base/executor.py
+++ b/tortoise/backends/base/executor.py
@@ -291,6 +291,7 @@ class BaseExecutor:
                     model=related_query.model,
                     annotations=related_query._annotations,
                     custom_filters=related_query._custom_filters,
+                    table=related_query_table,
                 )
 
             where_criterion, joins, having_criterion = modifier.get_query_modifiers()
diff --git a/tortoise/functions.py b/tortoise/functions.py
index 29cc269..c0c9da9 100644
--- a/tortoise/functions.py
+++ b/tortoise/functions.py
@@ -5,6 +5,7 @@ from pypika.terms import AggregateFunction
 from pypika.terms import Function as BaseFunction
 
 from tortoise.exceptions import ConfigurationError
+from tortoise.fields.relational import ForeignKeyFieldInstance
 
 ##############################################################################
 # Base
@@ -23,18 +24,18 @@ class Function:
         self.field_object: Any = None
         self.default_values = default_values
 
-    def _resolve_field_for_model(self, model, field: str, *default_values) -> dict:
+    def _resolve_field_for_model(self, model, table, field: str, *default_values) -> dict:
         field_split = field.split("__")
         if not field_split[1:]:
             function_joins = []
             if field_split[0] in model._meta.fetch_fields:
                 related_field = model._meta.fields_map[field_split[0]]
                 related_field_meta = related_field.model_class._meta
-                join = (model._meta.basetable, field_split[0], related_field)
+                join = (table, field_split[0], related_field)
                 function_joins.append(join)
                 field = related_field_meta.basetable[related_field_meta.db_pk_field]
             else:
-                field = model._meta.basetable[field_split[0]]
+                field = table[field_split[0]]
 
                 if self.populate_field_object:
                     self.field_object = model._meta.fields_map.get(field_split[0], None)
@@ -51,15 +52,19 @@ class Function:
         if field_split[0] not in model._meta.fetch_fields:
             raise ConfigurationError(f"{field} not resolvable")
         related_field = model._meta.fields_map[field_split[0]]
-        join = (model._meta.basetable, field_split[0], related_field)
+        join = (table, field_split[0], related_field)
+        related_table = related_field.model_class._meta.basetable
+        if isinstance(related_field, ForeignKeyFieldInstance):
+            # Only FK's can be to same table, so we only auto-alias FK join tables
+            related_table = related_table.as_(f"{table.get_table_name()}__{field_split[0]}")
         function = self._resolve_field_for_model(
-            related_field.model_class, "__".join(field_split[1:]), *default_values
+            related_field.model_class, related_table, "__".join(field_split[1:]), *default_values
         )
         function["joins"].append(join)
         return function
 
-    def resolve(self, model) -> dict:
-        function = self._resolve_field_for_model(model, self.field, *self.default_values)
+    def resolve(self, model, table) -> dict:
+        function = self._resolve_field_for_model(model, table, self.field, *self.default_values)
         function["joins"] = reversed(function["joins"])
         return function
 
diff --git a/tortoise/query_utils.py b/tortoise/query_utils.py
index 4bdcf89..1e77caa 100644
--- a/tortoise/query_utils.py
+++ b/tortoise/query_utils.py
@@ -8,9 +8,10 @@ from tortoise.exceptions import FieldError, OperationalError
 from tortoise.fields.relational import BackwardFKRelation, ManyToManyFieldInstance
 
 
-def _process_filter_kwarg(model, key, value) -> Tuple[Criterion, Optional[Tuple[Table, Criterion]]]:
+def _process_filter_kwarg(
+    model, key, value, table
+) -> Tuple[Criterion, Optional[Tuple[Table, Criterion]]]:
     join = None
-    table = model._meta.basetable
 
     if value is None and f"{key}__isnull" in model._meta.filters:
         param = model._meta.get_filter(f"{key}__isnull")
@@ -46,8 +47,10 @@ def _get_joins_for_related_field(
     table_pk = related_field.model._meta.db_pk_field
     related_table_pk = related_field.model_class._meta.db_pk_field
 
+    related_table = (
+        related_field.model_class._meta.basetable
+    )  # .as_(f"{table.get_table_name()}__{related_field_name}")
     if isinstance(related_field, ManyToManyFieldInstance):
-        related_table = related_field.model_class._meta.basetable
         through_table = Table(related_field.through)
         required_joins.append(
             (
@@ -63,7 +66,6 @@ def _get_joins_for_related_field(
             )
         )
     elif isinstance(related_field, BackwardFKRelation):
-        related_table = related_field.model_class._meta.basetable
         required_joins.append(
             (
                 related_table,
@@ -71,7 +73,7 @@ def _get_joins_for_related_field(
             )
         )
     else:
-        related_table = related_field.model_class._meta.basetable
+        related_table = related_table.as_(f"{table.get_table_name()}__{related_field_name}")
         required_joins.append(
             (
                 related_table,
@@ -206,9 +208,7 @@ class Q:
     def negate(self) -> None:
         self._is_negated = not self._is_negated
 
-    def _resolve_nested_filter(self, model, key, value) -> QueryModifier:
-        table = model._meta.basetable
-
+    def _resolve_nested_filter(self, model, key, value, table) -> QueryModifier:
         related_field_name = key.split("__")[0]
         related_field = model._meta.fields_map[related_field_name]
         required_joins = _get_joins_for_related_field(table, related_field, related_field_name)
@@ -216,14 +216,15 @@ class Q:
             model=related_field.model_class,
             annotations=self._annotations,
             custom_filters=self._custom_filters,
+            table=required_joins[-1][0],
         )
 
         return QueryModifier(joins=required_joins) & modifier
 
-    def _resolve_custom_kwarg(self, model, key, value) -> QueryModifier:
+    def _resolve_custom_kwarg(self, model, key, value, table) -> QueryModifier:
         having_info = self._custom_filters[key]
         annotation = self._annotations[having_info["field"]]
-        annotation_info = annotation.resolve(model)
+        annotation_info = annotation.resolve(model, table)
         operator = having_info["operator"]
         overridden_operator = model._meta.db.executor_class.get_overridden_filter_func(
             filter_func=operator
@@ -236,11 +237,11 @@ class Q:
             modifier = QueryModifier(where_criterion=operator(annotation_info["field"], value))
         return modifier
 
-    def _resolve_regular_kwarg(self, model, key, value) -> QueryModifier:
+    def _resolve_regular_kwarg(self, model, key, value, table) -> QueryModifier:
         if key not in model._meta.filters and key.split("__")[0] in model._meta.fetch_fields:
-            modifier = self._resolve_nested_filter(model, key, value)
+            modifier = self._resolve_nested_filter(model, key, value, table)
         else:
-            criterion, join = _process_filter_kwarg(model, key, value)
+            criterion, join = _process_filter_kwarg(model, key, value, table)
             joins = [join] if join else []
             modifier = QueryModifier(where_criterion=criterion, joins=joins)
         return modifier
@@ -273,14 +274,14 @@ class Q:
             raise FieldError(f"Unknown filter param '{key}'. Allowed base values are {allowed}")
         return filter_key, filter_value
 
-    def _resolve_kwargs(self, model) -> QueryModifier:
+    def _resolve_kwargs(self, model, table) -> QueryModifier:
         modifier = QueryModifier()
         for raw_key, raw_value in self.filters.items():
             key, value = self._get_actual_filter_params(model, raw_key, raw_value)
             if key in self._custom_filters:
-                filter_modifier = self._resolve_custom_kwarg(model, key, value)
+                filter_modifier = self._resolve_custom_kwarg(model, key, value, table)
             else:
-                filter_modifier = self._resolve_regular_kwarg(model, key, value)
+                filter_modifier = self._resolve_regular_kwarg(model, key, value, table)
 
             if self.join_type == self.AND:
                 modifier &= filter_modifier
@@ -290,10 +291,10 @@ class Q:
             modifier = ~modifier
         return modifier
 
-    def _resolve_children(self, model) -> QueryModifier:
+    def _resolve_children(self, model, table) -> QueryModifier:
         modifier = QueryModifier()
         for node in self.children:
-            node_modifier = node.resolve(model, self._annotations, self._custom_filters)
+            node_modifier = node.resolve(model, self._annotations, self._custom_filters, table)
             if self.join_type == self.AND:
                 modifier &= node_modifier
             else:
@@ -303,12 +304,12 @@ class Q:
             modifier = ~modifier
         return modifier
 
-    def resolve(self, model, annotations, custom_filters) -> QueryModifier:
+    def resolve(self, model, annotations, custom_filters, table) -> QueryModifier:
         self._annotations = annotations
         self._custom_filters = custom_filters
         if self.filters:
-            return self._resolve_kwargs(model)
-        return self._resolve_children(model)
+            return self._resolve_kwargs(model, table)
+        return self._resolve_children(model, table)
 
 
 class Prefetch:
diff --git a/tortoise/queryset.py b/tortoise/queryset.py
index 346cb5c..1ba0410 100644
--- a/tortoise/queryset.py
+++ b/tortoise/queryset.py
@@ -57,7 +57,7 @@ class AwaitableQuery(Generic[MODEL]):
     def resolve_filters(self, model, q_objects, annotations, custom_filters) -> None:
         modifier = QueryModifier()
         for node in q_objects:
-            modifier &= node.resolve(model, annotations, custom_filters)
+            modifier &= node.resolve(model, annotations, custom_filters, model._meta.basetable)
 
         where_criterion, joins, having_criterion = modifier.get_query_modifiers()
         for join in joins:
@@ -68,15 +68,15 @@ class AwaitableQuery(Generic[MODEL]):
         self.query._wheres = where_criterion
         self.query._havings = having_criterion
 
-    def _join_table_by_field(self, table, related_field_name, related_field) -> None:
+    def _join_table_by_field(self, table, related_field_name, related_field) -> Table:
         joins = _get_joins_for_related_field(table, related_field, related_field_name)
         for join in joins:
             if join[0] not in self._joined_tables:
                 self.query = self.query.join(join[0], how=JoinType.left_outer).on(join[1])
                 self._joined_tables.append(join[0])
+        return joins[-1][0]
 
-    def resolve_ordering(self, model, orderings, annotations) -> None:
-        table = model._meta.basetable
+    def resolve_ordering(self, model, table, orderings, annotations) -> None:
         for ordering in orderings:
             field_name = ordering[0]
             if field_name in model._meta.fetch_fields:
@@ -86,15 +86,16 @@ class AwaitableQuery(Generic[MODEL]):
             if field_name.split("__")[0] in model._meta.fetch_fields:
                 related_field_name = field_name.split("__")[0]
                 related_field = model._meta.fields_map[related_field_name]
-                self._join_table_by_field(table, related_field_name, related_field)
+                related_table = self._join_table_by_field(table, related_field_name, related_field)
                 self.resolve_ordering(
                     related_field.model_class,
+                    related_table,
                     [("__".join(field_name.split("__")[1:]), ordering[1])],
                     {},
                 )
             elif field_name in annotations:
                 annotation = annotations[field_name]
-                annotation_info = annotation.resolve(self.model)
+                annotation_info = annotation.resolve(self.model, table)
                 self.query = self.query.orderby(annotation_info["field"], order=ordering[1])
             else:
                 field_object = self.model._meta.fields_map.get(field_name)
@@ -493,14 +494,14 @@ class QuerySet(AwaitableQuery[MODEL]):
             return
         table = self.model._meta.basetable
         if any(
-            annotation.resolve(self.model)["field"].is_aggregate
+            annotation.resolve(self.model, table)["field"].is_aggregate
             for annotation in self._annotations.values()
         ):
             self.query = self.query.groupby(table.id)
         for key, annotation in self._annotations.items():
-            annotation_info = annotation.resolve(self.model)
+            annotation_info = annotation.resolve(self.model, table)
             for join in annotation_info["joins"]:
-                self._join_table_by_field(*join)
+                table = self._join_table_by_field(*join)
             self.query._select_other(annotation_info["field"].as_(key))
 
     def _make_query(self) -> None:
@@ -518,7 +519,9 @@ class QuerySet(AwaitableQuery[MODEL]):
             self.query._offset = self._offset
         if self._distinct:
             self.query._distinct = True
-        self.resolve_ordering(self.model, self._orderings, self._annotations)
+        self.resolve_ordering(
+            self.model, self.model._meta.basetable, self._orderings, self._annotations
+        )
 
     def __await__(self) -> Generator[Any, None, List[MODEL]]:
         if self._db is None:
@@ -677,9 +680,8 @@ class FieldSelectQuery(AwaitableQuery):
         self.annotations = annotations
 
     def _join_table_with_forwarded_fields(
-        self, model, field: str, forwarded_fields: str
+        self, model, table, field: str, forwarded_fields: str
     ) -> Tuple[Table, str]:
-        table = model._meta.basetable
         if field in model._meta.fields_db_projection and not forwarded_fields:
             return table, model._meta.fields_db_projection[field]
 
@@ -696,11 +698,12 @@ class FieldSelectQuery(AwaitableQuery):
         if not field_object:
             raise FieldError(f'Unknown field "{field}" for model "{model.__name__}"')
 
-        self._join_table_by_field(table, field, field_object)
+        table = self._join_table_by_field(table, field, field_object)
         forwarded_fields_split = forwarded_fields.split("__")
 
         return self._join_table_with_forwarded_fields(
             model=field_object.model_class,
+            table=table,
             field=forwarded_fields_split[0],
             forwarded_fields="__".join(forwarded_fields_split[1:]),
         )
@@ -720,14 +723,17 @@ class FieldSelectQuery(AwaitableQuery):
 
         if field in self.annotations:
             annotation = self.annotations[field]
-            annotation_info = annotation.resolve(self.model)
+            annotation_info = annotation.resolve(self.model, table)
             self.query._select_other(annotation_info["field"].as_(return_as))
             return
 
         field_split = field.split("__")
         if field_split[0] in self.model._meta.fetch_fields:
             related_table, related_db_field = self._join_table_with_forwarded_fields(
-                model=self.model, field=field_split[0], forwarded_fields="__".join(field_split[1:])
+                model=self.model,
+                table=table,
+                field=field_split[0],
+                forwarded_fields="__".join(field_split[1:]),
             )
             self.query._select_field(getattr(related_table, related_db_field).as_(return_as))
             return
@@ -820,7 +826,9 @@ class ValuesListQuery(FieldSelectQuery):
             self.query._offset = self.offset
         if self.distinct:
             self.query._distinct = True
-        self.resolve_ordering(self.model, self.orderings, self.annotations)
+        self.resolve_ordering(
+            self.model, self.model._meta.basetable, self.orderings, self.annotations
+        )
 
     def __await__(self) -> Generator[Any, None, List[Any]]:
         if self._db is None:
@@ -899,7 +907,9 @@ class ValuesQuery(FieldSelectQuery):
             self.query._offset = self.offset
         if self.distinct:
             self.query._distinct = True
-        self.resolve_ordering(self.model, self.orderings, self.annotations)
+        self.resolve_ordering(
+            self.model, self.model._meta.basetable, self.orderings, self.annotations
+        )
 
     def __await__(self) -> Generator[Any, None, List[dict]]:
         if self._db is None:


Test patch:
diff --git a/tests/test_q.py b/tests/test_q.py
index 31d9b26..8ef9a38 100644
--- a/tests/test_q.py
+++ b/tests/test_q.py
@@ -96,57 +96,57 @@ class TestQ(_TestCase):
 class TestQCall(TestCase):
     def test_q_basic(self):
         q = Q(id=8)
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id"=8')
 
     def test_q_basic_and(self):
         q = Q(join_type="AND", id=8)
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id"=8')
 
     def test_q_basic_or(self):
         q = Q(join_type="OR", id=8)
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id"=8')
 
     def test_q_multiple_and(self):
         q = Q(join_type="AND", id__gt=8, id__lt=10)
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id">8 AND "id"<10')
 
     def test_q_multiple_or(self):
         q = Q(join_type="OR", id__gt=8, id__lt=10)
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id">8 OR "id"<10')
 
     def test_q_multiple_and2(self):
         q = Q(join_type="AND", id=8, intnum=80)
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id"=8 AND "intnum"=80')
 
     def test_q_multiple_or2(self):
         q = Q(join_type="OR", id=8, intnum=80)
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id"=8 OR "intnum"=80')
 
     def test_q_complex_int(self):
         q = Q(Q(intnum=80), Q(id__lt=5, id__gt=50, join_type="OR"), join_type="AND")
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"intnum"=80 AND ("id"<5 OR "id">50)')
 
     def test_q_complex_int2(self):
         q = Q(Q(intnum="80"), Q(Q(id__lt="5"), Q(id__gt="50"), join_type="OR"), join_type="AND")
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"intnum"=80 AND ("id"<5 OR "id">50)')
 
     def test_q_complex_int3(self):
         q = Q(Q(id__lt=5, id__gt=50, join_type="OR"), join_type="AND", intnum=80)
-        r = q.resolve(IntFields, {}, {})
+        r = q.resolve(IntFields, {}, {}, IntFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"intnum"=80 AND ("id"<5 OR "id">50)')
 
     def test_q_complex_char(self):
         q = Q(Q(char_null=80), ~Q(char__lt=5, char__gt=50, join_type="OR"), join_type="AND")
-        r = q.resolve(CharFields, {}, {})
+        r = q.resolve(CharFields, {}, {}, CharFields._meta.basequery)
         self.assertEqual(
             r.where_criterion.get_sql(),
             "\"char_null\"='80' AND NOT (\"char\"<'5' OR \"char\">'50')",
@@ -158,7 +158,7 @@ class TestQCall(TestCase):
             ~Q(Q(char__lt="5"), Q(char__gt="50"), join_type="OR"),
             join_type="AND",
         )
-        r = q.resolve(CharFields, {}, {})
+        r = q.resolve(CharFields, {}, {}, CharFields._meta.basequery)
         self.assertEqual(
             r.where_criterion.get_sql(),
             "\"char_null\"='80' AND NOT (\"char\"<'5' OR \"char\">'50')",
@@ -166,7 +166,7 @@ class TestQCall(TestCase):
 
     def test_q_complex_char3(self):
         q = Q(~Q(char__lt=5, char__gt=50, join_type="OR"), join_type="AND", char_null=80)
-        r = q.resolve(CharFields, {}, {})
+        r = q.resolve(CharFields, {}, {}, CharFields._meta.basequery)
         self.assertEqual(
             r.where_criterion.get_sql(),
             "\"char_null\"='80' AND NOT (\"char\"<'5' OR \"char\">'50')",
@@ -174,30 +174,30 @@ class TestQCall(TestCase):
 
     def test_q_with_blank_and(self):
         q = Q(Q(id__gt=5), Q(), join_type=Q.AND)
-        r = q.resolve(CharFields, {}, {})
+        r = q.resolve(CharFields, {}, {}, CharFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id">5')
 
     def test_q_with_blank_or(self):
         q = Q(Q(id__gt=5), Q(), join_type=Q.OR)
-        r = q.resolve(CharFields, {}, {})
+        r = q.resolve(CharFields, {}, {}, CharFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id">5')
 
     def test_q_with_blank_and2(self):
         q = Q(id__gt=5) & Q()
-        r = q.resolve(CharFields, {}, {})
+        r = q.resolve(CharFields, {}, {}, CharFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id">5')
 
     def test_q_with_blank_or2(self):
         q = Q(id__gt=5) | Q()
-        r = q.resolve(CharFields, {}, {})
+        r = q.resolve(CharFields, {}, {}, CharFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id">5')
 
     def test_q_with_blank_and3(self):
         q = Q() & Q(id__gt=5)
-        r = q.resolve(CharFields, {}, {})
+        r = q.resolve(CharFields, {}, {}, CharFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id">5')
 
     def test_q_with_blank_or3(self):
         q = Q() | Q(id__gt=5)
-        r = q.resolve(CharFields, {}, {})
+        r = q.resolve(CharFields, {}, {}, CharFields._meta.basequery)
         self.assertEqual(r.where_criterion.get_sql(), '"id">5')
diff --git a/tests/test_relations.py b/tests/test_relations.py
index f1dc7a6..8222f5a 100644
--- a/tests/test_relations.py
+++ b/tests/test_relations.py
@@ -1,4 +1,4 @@
-from tests.testmodels import Address, Employee, Event, Reporter, Team, Tournament
+from tests.testmodels import Address, DoubleFK, Employee, Event, Reporter, Team, Tournament
 from tortoise.contrib import test
 from tortoise.exceptions import FieldError, NoValuesFetched
 from tortoise.functions import Count
@@ -242,3 +242,82 @@ class TestRelations(test.TestCase):
 
         self.assertFalse(event1.reporter)
         self.assertTrue(event2.reporter)
+
+
+class TestDoubleFK(test.TestCase):
+    select_match = r'SELECT [`"]doublefk[`"].[`"]name[`"] [`"]name[`"]'
+    select1_match = r'[`"]doublefk__left[`"].[`"]name[`"] [`"]left__name[`"]'
+    select2_match = r'[`"]doublefk__right[`"].[`"]name[`"] [`"]right__name[`"]'
+    join1_match = (
+        r'LEFT OUTER JOIN [`"]doublefk[`"] [`"]doublefk__left[`"] ON '
+        r'[`"]doublefk__left[`"].[`"]id[`"]=[`"]doublefk[`"].[`"]left_id[`"]'
+    )
+    join2_match = (
+        r'LEFT OUTER JOIN [`"]doublefk[`"] [`"]doublefk__right[`"] ON '
+        r'[`"]doublefk__right[`"].[`"]id[`"]=[`"]doublefk[`"].[`"]right_id[`"]'
+    )
+
+    async def setUp(self) -> None:
+        one = await DoubleFK.create(name="one")
+        two = await DoubleFK.create(name="two")
+        self.middle = await DoubleFK.create(name="middle", left=one, right=two)
+
+    async def test_doublefk_filter(self):
+        qset = DoubleFK.filter(left__name="one")
+        result = await qset
+        query = qset.query.get_sql()
+
+        self.assertRegex(query, self.join1_match)
+        self.assertEqual(result, [self.middle])
+
+    async def test_doublefk_filter_values(self):
+        qset = DoubleFK.filter(left__name="one").values("name")
+        result = await qset
+        query = qset.query.get_sql()
+
+        self.assertRegex(query, self.select_match)
+        self.assertRegex(query, self.join1_match)
+        self.assertEqual(result, [{"name": "middle"}])
+
+    async def test_doublefk_filter_values_rel(self):
+        qset = DoubleFK.filter(left__name="one").values("name", "left__name")
+        result = await qset
+        query = qset.query.get_sql()
+
+        self.assertRegex(query, self.select_match)
+        self.assertRegex(query, self.select1_match)
+        self.assertRegex(query, self.join1_match)
+        self.assertEqual(result, [{"name": "middle", "left__name": "one"}])
+
+    async def test_doublefk_filter_both(self):
+        qset = DoubleFK.filter(left__name="one", right__name="two")
+        result = await qset
+        query = qset.query.get_sql()
+
+        self.assertRegex(query, self.join1_match)
+        self.assertRegex(query, self.join2_match)
+        self.assertEqual(result, [self.middle])
+
+    async def test_doublefk_filter_both_values(self):
+        qset = DoubleFK.filter(left__name="one", right__name="two").values("name")
+        result = await qset
+        query = qset.query.get_sql()
+
+        self.assertRegex(query, self.select_match)
+        self.assertRegex(query, self.join1_match)
+        self.assertRegex(query, self.join2_match)
+        self.assertEqual(result, [{"name": "middle"}])
+
+    async def test_doublefk_filter_both_values_rel(self):
+        qset = DoubleFK.filter(left__name="one", right__name="two").values(
+            "name", "left__name", "right__name"
+        )
+        result = await qset
+        query = qset.query.get_sql()
+
+        self.assertRegex(query, self.select_match)
+        self.assertRegex(query, self.select1_match)
+        self.assertRegex(query, self.select2_match)
+        self.assertRegex(query, self.join1_match)
+        self.assertRegex(query, self.join2_match)
+        self.assertEqual(result, [{"name": "middle", "left__name": "one", "right__name": "two"}])
diff --git a/tests/testmodels.py b/tests/testmodels.py
index 6d40ceb..66ab3e1 100644
--- a/tests/testmodels.py
+++ b/tests/testmodels.py
@@ -508,3 +508,9 @@ class Currency(str, Enum):
 class EnumFields(Model):
     service: Service = fields.IntEnumField(Service)
     currency: Currency = fields.CharEnumField(Currency, default=Currency.HUF)
+
+
+class DoubleFK(Model):
+    name = fields.CharField(max_length=50)
+    left = fields.ForeignKeyField("models.DoubleFK", null=True, related_name="left_rel")
+    right = fields.ForeignKeyField("models.DoubleFK", null=True, related_name="right_rel")