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)

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:

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";
```

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")

pip install -e .
parse_log_pytest
requirements.txt
pytest pytest-cov pytest-xdist pytest-mock pytest-asyncio
3.7
tests/requirements.txt
pytest --no-header -rA --tb=line --color=no -p no:cacheprovider -W ignore::DeprecationWarning
aiofiles==0.4.0
aiomysql==0.0.20
aiosqlite==0.11.0
alabaster==0.7.12
apipkg==1.5
appdirs==1.4.3
astroid==2.3.3
asyncpg==0.20.0
asynctest==0.13.0
attrs==19.3.0
Babel==2.8.0
bandit==1.6.2
black==19.10b0
bleach==3.1.0
blinker==1.4
certifi==2019.11.28
cffi==1.13.2
chardet==3.0.4
ciso8601==2.1.2
Click==7.0
cloud-sptheme==1.10.0
colorama==0.4.3
coverage==5.0.2
coveralls==1.10.0
cryptography==2.8
docopt==0.6.2
docutils==0.15.2
entrypoints==0.3
exceptiongroup==1.2.2
execnet==1.7.1
filelock==3.0.12
flake8==3.7.9
flake8-isort==2.8.0
gitdb2==2.0.6
GitPython==3.0.5
green==3.1.0
h11==0.8.1
h2==3.1.1
hpack==3.0.0
hstspreload==2020.1.7
httptools==0.0.13
httpx==0.9.3
Hypercorn==0.9.0
hyperframe==5.2.0
idna==2.8
imagesize==1.2.0
importlib-metadata==1.3.0
iniconfig==2.0.0
isort==4.3.21
itsdangerous==1.1.0
Jinja2==2.10.3
lazy-object-proxy==1.4.3
lxml==4.4.2
MarkupSafe==1.1.1
mccabe==0.6.1
more-itertools==8.0.2
multidict==4.7.3
mypy==0.761
mypy-extensions==0.4.3
nose2==0.9.1
packaging==20.0
pathspec==0.7.0
pbr==5.4.4
pip-tools==4.3.0
pkginfo==1.5.0.1
pluggy==0.13.1
priority==1.3.0
py==1.8.1
pycodestyle==2.5.0
pycparser==2.19
pyflakes==2.1.1
Pygments==2.5.2
pylint==2.4.4
PyMySQL==0.9.2
pyparsing==2.4.6
PyPika==0.35.18
pytest==7.4.4
pytest-asyncio==0.21.2
pytest-cov==2.8.1
pytest-forked==1.1.3
pytest-mock==3.11.1
pytest-xdist==1.31.0
python-rapidjson==0.9.1
pytz==2019.3
PyYAML==5.3
Quart==0.10.0
readme-renderer==24.0
regex==2020.1.7
requests==2.22.0
requests-toolbelt==0.9.1
rfc3986==1.3.2
sanic==19.12.2
six==1.13.0
smmap2==2.0.5
sniffio==1.1.0
snowballstemmer==2.0.0
sortedcontainers==2.1.0
Sphinx==1.8.5
sphinxcontrib-websupport==1.1.2
starlette==0.13.0
stevedore==1.31.0
testfixtures==6.10.3
toml==0.10.0
tomli==2.0.1
-e git+https://github.com/tortoise/tortoise-orm.git@33ccfd12a594daef66a6faec2f4fd50ef5e37aec#egg=tortoise_orm
tox==3.14.3
tqdm==4.41.1
twine==1.15.0
typed-ast==1.4.0
typing-extensions==3.7.4.1
ujson==1.35
Unidecode==1.1.1
urllib3==1.25.7
uvicorn==0.11.1
uvloop==0.14.0
virtualenv==16.7.9
wcwidth==0.1.8
webencodings==0.5.1
websockets==8.1
wrapt==1.11.2
wsproto==0.15.0
zipp==0.6.0

name: tortoise-orm
channels:
  - defaults
  - https://repo.anaconda.com/pkgs/main
  - https://repo.anaconda.com/pkgs/r
  - conda-forge
dependencies:
  - _libgcc_mutex=0.1=main
  - _openmp_mutex=5.1=1_gnu
  - ca-certificates=2025.2.25=h06a4308_0
  - ld_impl_linux-64=2.40=h12ee557_0
  - libffi=3.4.4=h6a678d5_1
  - libgcc-ng=11.2.0=h1234567_1
  - libgomp=11.2.0=h1234567_1
  - libstdcxx-ng=11.2.0=h1234567_1
  - ncurses=6.4=h6a678d5_0
  - openssl=1.1.1w=h7f8727e_0
  - pip=22.3.1=py37h06a4308_0
  - python=3.7.16=h7a1cb2a_0
  - readline=8.2=h5eee18b_0
  - setuptools=65.6.3=py37h06a4308_0
  - sqlite=3.45.3=h5eee18b_0
  - tk=8.6.14=h39e8969_0
  - wheel=0.38.4=py37h06a4308_0
  - xz=5.6.4=h5eee18b_1
  - zlib=1.2.13=h5eee18b_1
  - pip:
      - aiofiles==0.4.0
      - aiomysql==0.0.20
      - aiosqlite==0.11.0
      - alabaster==0.7.12
      - apipkg==1.5
      - appdirs==1.4.3
      - astroid==2.3.3
      - asyncpg==0.20.0
      - asynctest==0.13.0
      - attrs==19.3.0
      - babel==2.8.0
      - bandit==1.6.2
      - black==19.10b0
      - bleach==3.1.0
      - blinker==1.4
      - certifi==2019.11.28
      - cffi==1.13.2
      - chardet==3.0.4
      - ciso8601==2.1.2
      - click==7.0
      - cloud-sptheme==1.10.0
      - colorama==0.4.3
      - coverage==5.0.2
      - coveralls==1.10.0
      - cryptography==2.8
      - docopt==0.6.2
      - docutils==0.15.2
      - entrypoints==0.3
      - exceptiongroup==1.2.2
      - execnet==1.7.1
      - filelock==3.0.12
      - flake8==3.7.9
      - flake8-isort==2.8.0
      - gitdb2==2.0.6
      - gitpython==3.0.5
      - green==3.1.0
      - h11==0.8.1
      - h2==3.1.1
      - hpack==3.0.0
      - hstspreload==2020.1.7
      - httptools==0.0.13
      - httpx==0.9.3
      - hypercorn==0.9.0
      - hyperframe==5.2.0
      - idna==2.8
      - imagesize==1.2.0
      - importlib-metadata==1.3.0
      - iniconfig==2.0.0
      - isort==4.3.21
      - itsdangerous==1.1.0
      - jinja2==2.10.3
      - lazy-object-proxy==1.4.3
      - lxml==4.4.2
      - markupsafe==1.1.1
      - mccabe==0.6.1
      - more-itertools==8.0.2
      - multidict==4.7.3
      - mypy==0.761
      - mypy-extensions==0.4.3
      - nose2==0.9.1
      - packaging==20.0
      - pathspec==0.7.0
      - pbr==5.4.4
      - pip-tools==4.3.0
      - pkginfo==1.5.0.1
      - pluggy==0.13.1
      - priority==1.3.0
      - py==1.8.1
      - pycodestyle==2.5.0
      - pycparser==2.19
      - pyflakes==2.1.1
      - pygments==2.5.2
      - pylint==2.4.4
      - pymysql==0.9.2
      - pyparsing==2.4.6
      - pypika==0.35.18
      - pytest==7.4.4
      - pytest-asyncio==0.21.2
      - pytest-cov==2.8.1
      - pytest-forked==1.1.3
      - pytest-mock==3.11.1
      - pytest-xdist==1.31.0
      - python-rapidjson==0.9.1
      - pytz==2019.3
      - pyyaml==5.3
      - quart==0.10.0
      - readme-renderer==24.0
      - regex==2020.1.7
      - requests==2.22.0
      - requests-toolbelt==0.9.1
      - rfc3986==1.3.2
      - sanic==19.12.2
      - six==1.13.0
      - smmap2==2.0.5
      - sniffio==1.1.0
      - snowballstemmer==2.0.0
      - sortedcontainers==2.1.0
      - sphinx==1.8.5
      - sphinxcontrib-websupport==1.1.2
      - starlette==0.13.0
      - stevedore==1.31.0
      - testfixtures==6.10.3
      - toml==0.10.0
      - tomli==2.0.1
      - tox==3.14.3
      - tqdm==4.41.1
      - twine==1.15.0
      - typed-ast==1.4.0
      - typing-extensions==3.7.4.1
      - ujson==1.35
      - unidecode==1.1.1
      - urllib3==1.25.7
      - uvicorn==0.11.1
      - uvloop==0.14.0
      - virtualenv==16.7.9
      - wcwidth==0.1.8
      - webencodings==0.5.1
      - websockets==8.1
      - wrapt==1.11.2
      - wsproto==0.15.0
      - zipp==0.6.0
prefix: /opt/conda/envs/tortoise-orm

tests/test_q.py::TestQCall::test_q_with_blank_or tests/test_q.py::TestQCall::test_q_with_blank_and2 tests/test_q.py::TestQCall::test_q_with_blank_or2 tests/test_q.py::TestQCall::test_q_basic_or tests/test_relations.py::TestDoubleFK::test_doublefk_filter_both_values tests/test_q.py::TestQCall::test_q_complex_int3 tests/test_q.py::TestQCall::test_q_complex_char2 tests/test_q.py::TestQCall::test_q_with_blank_or3 tests/test_q.py::TestQCall::test_q_with_blank_and tests/test_q.py::TestQCall::test_q_complex_char tests/test_q.py::TestQCall::test_q_complex_char3 tests/test_relations.py::TestDoubleFK::test_doublefk_filter_values tests/test_q.py::TestQCall::test_q_complex_int2 tests/test_relations.py::TestDoubleFK::test_doublefk_filter tests/test_q.py::TestQCall::test_q_basic tests/test_q.py::TestQCall::test_q_multiple_and2 tests/test_q.py::TestQCall::test_q_basic_and tests/test_q.py::TestQCall::test_q_multiple_and tests/test_q.py::TestQCall::test_q_multiple_or2 tests/test_relations.py::TestDoubleFK::test_doublefk_filter_both tests/test_q.py::TestQCall::test_q_with_blank_and3 tests/test_q.py::TestQCall::test_q_complex_int tests/test_relations.py::TestDoubleFK::test_doublefk_filter_values_rel tests/test_relations.py::TestDoubleFK::test_doublefk_filter_both_values_rel tests/test_q.py::TestQCall::test_q_multiple_or
tests/test_q.py::TestQ::test_q_notq tests/test_q.py::TestQ::test_q_compound_and_notq tests/test_q.py::TestQ::test_q_compound_or_notq tests/test_q.py::TestQ::test_q_partial_or tests/test_q.py::TestQ::test_q_partial_and tests/test_relations.py::TestRelations::test_bool_for_relation_new_object tests/test_q.py::TestQ::test_q_basic tests/test_q.py::TestQ::test_q_bad_join_type tests/test_q.py::TestQ::test_q_compound tests/test_relations.py::TestRelations::test_m2m_clear tests/test_relations.py::TestRelations::test_bool_for_relation_fetched_true tests/test_q.py::TestQ::test_q_compound_or tests/test_q.py::TestQ::test_q_compound_and tests/test_relations.py::TestRelations::test_bool_for_relation_fetched_false tests/test_relations.py::TestRelations::test_prefetch_related_rfk tests/test_relations.py::TestRelations::test_m2m_remove_two tests/test_relations.py::TestRelations::test_nullable_fk_obj tests/test_relations.py::TestRelations::test_bool_for_relation_old_object tests/test_relations.py::TestRelations::test_prefetch_related_nonrel_field tests/test_relations.py::TestRelations::test_prefetch_related_id tests/test_relations.py::TestRelations::test_m2m_remove tests/test_relations.py::TestRelations::test_m2m_add_already_added tests/test_relations.py::TestRelations::test_prefetch_related_fk tests/test_relations.py::TestRelations::test_o2o_lazy tests/test_relations.py::TestRelations::test_reset_queryset_on_query tests/test_relations.py::TestRelations::test_prefetch_related_missing_field tests/test_relations.py::TestRelations::test_nullable_fk_raw tests/test_relations.py::TestRelations::test_relations tests/test_relations.py::TestRelations::test_m2m_add tests/test_relations.py::TestRelations::test_self_ref
tortoise/backends/base/executor.py tortoise/functions.py CHANGELOG.rst tortoise/queryset.py tortoise/query_utils.py
tortoise/backends/base/executor.py tortoise/functions.py CHANGELOG.rst tortoise/queryset.py tortoise/query_utils.py