id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36682	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:
{{{#!python
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()
}}}   

{{{#!python
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
{{{#!sql
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:  
{{{#!python
        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"	Bug	new	Database layer (models, ORM)	5.2	Normal		FilteredRelation		Accepted	0	0	0	0	0	0