RecursionError when traversing several reverse relations with FilteredRelation

[REGNIER Guillaume]

When declaring a filtered relation with a condition on the 2-distant reverse relation, query compilation fails with a RecursionError

Steps to Reproduce:

class Company(models.Model):
    pass

class Employee(models.Model):
    company = models.ForeignKey(Company, on_delete=models.CASCADE, related_name="employees")

class Mission(models.Model):
    employee = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name="missions")
    name = models.TextField()
    deadline = models.DateTimeField()

qs = Company.objects.alias(
    specific_mission=FilteredRelation(
        "employees__missions",
        condition=Q(employees__missions__name="specific"),
    )
)
qs = qs.filter(specific_mission__deadline__lt=localtime())
list(qs)

Expected Behavior:

.filter(...) does not throw RecursionError and produce an SQL query equivalent to

SELECT 
  "company"."id" 
FROM 
  "company" 
  INNER JOIN "employee" ON (
    "company"."id" = "employee"."company_id"
  ) 
  INNER JOIN "mission" specific_mission ON (
    "employee"."id" = specific_mission."employee_id" 
    AND specific_mission."name" = 'specific'
  ) 
WHERE 
  specific_mission."deadline" < '2025-10-23 11:07:06.224821+02:00'

Actual Behavior:

.filter(...) throws RecursionError

Analysis:

My understanding is that Query.join() fails to reuse the join for the first reverse relation, leading to the FilteredRelation being resolved again and again.
Loop is:

• FilteredRelation.resolve_expression()
• Query.build_filter()
• Query._add_q()
• Query.build_filter()
• Query.setup_joins()
• Query.join()
• FilteredRelation.resolve_expression()

Workaround:

        qs = Company.objects.alias(
            _employees=FilteredRelation("employees"),
            specific_mission=FilteredRelation(
                "_employees__missions",
                condition=Q(_employees__missions__name="specific"),
            ),
        )
        qs = qs.filter(specific_mission__deadline__lt=localtime())
        list(qs)

Related tickets:

#36109: Related to FilteredRelation and RecursionError (uses mentioned workaround)
#34957: Probably the same underlying issue, ticket was closed due to lacks reproduction details

[Simon Charette]

Thank you for your report, reproduced against 5e2bbebed9f36bb9d15f168444e7982287761877.