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
31657	"Self referencing foreign key doesn't correctly order by a relation ""_id"" field."	Jack Delany	Hasan Ramezani	"Initially discovered on 2.2.10 but verified still happens on 3.0.6.  Given the following models:
{{{
class OneModel(models.Model):
    class Meta:
        ordering = (""-id"",)

    id = models.BigAutoField(primary_key=True)
    root = models.ForeignKey(""OneModel"", on_delete=models.CASCADE, null=True)
    oneval = models.BigIntegerField(null=True)

class TwoModel(models.Model):
    id = models.BigAutoField(primary_key=True)
    record = models.ForeignKey(OneModel, on_delete=models.CASCADE)
    twoval = models.BigIntegerField(null=True)
}}}
The following queryset gives unexpected results and appears to be an incorrect SQL query:
{{{
qs = TwoModel.objects.filter(record__oneval__in=[1,2,3])
qs = qs.order_by(""record__root_id"")
print(qs.query)
}}}
{{{
SELECT ""orion_twomodel"".""id"", ""orion_twomodel"".""record_id"", ""orion_twomodel"".""twoval"" FROM ""orion_twomodel"" INNER JOIN ""orion_onemodel"" ON (""orion_twomodel"".""record_id"" = ""orion_onemodel"".""id"") LEFT OUTER JOIN ""orion_onemodel"" T3 ON (""orion_onemodel"".""root_id"" = T3.""id"") WHERE ""orion_onemodel"".""oneval"" IN (1, 2, 3) ORDER BY T3.""id"" DESC
}}}
The query has an unexpected DESCENDING sort.  That appears to come from the default sort order on the OneModel class, but I would expect the order_by() to take prececence.  The the query has two JOINS, which is unnecessary.  It appears that, since OneModel.root is a foreign key to itself, that is causing it to do the unnecessary extra join.  In fact, testing a model where root is a foreign key to a third model doesn't show the problem behavior.

Note also that the queryset with `order_by(""record__root"")` gives the exact same SQL.

This queryset gives correct results and what looks like a pretty optimal SQL:
{{{
qs = TwoModel.objects.filter(record__oneval__in=[1,2,3])
qs = qs.order_by(""record__root__id"")
print(qs.query)
}}}
{{{
SELECT ""orion_twomodel"".""id"", ""orion_twomodel"".""record_id"", ""orion_twomodel"".""twoval"" FROM ""orion_twomodel"" INNER JOIN ""orion_onemodel"" ON (""orion_twomodel"".""record_id"" = ""orion_onemodel"".""id"") WHERE ""orion_onemodel"".""oneval"" IN (1, 2, 3) ORDER BY ""orion_onemodel"".""root_id"" ASC
}}}

So is this a potential bug or a misunderstanding on my part?

Another queryset that works around the issue and gives a reasonable SQL query and expected results:
{{{
qs = TwoModel.objects.filter(record__oneval__in=[1,2,3])
qs = qs.annotate(root_id=F(""record__root_id""))
qs = qs.order_by(""root_id"")
print(qs.query)
}}}
{{{
SELECT ""orion_twomodel"".""id"", ""orion_twomodel"".""record_id"", ""orion_twomodel"".""twoval"" FROM ""orion_twomodel"" INNER JOIN ""orion_onemodel"" ON (""orion_twomodel"".""record_id"" = ""orion_onemodel"".""id"") WHERE ""orion_onemodel"".""oneval"" IN (1, 2, 3) ORDER BY ""orion_onemodel"".""zero_id"" ASC
}}}
ASCENDING sort, and a single INNER JOIN, as I'd expect.  That actually works for my use because I need that output column anyway.

One final oddity; with the original queryset but the inverted sort order_by():
{{{
qs = TwoModel.objects.filter(record__oneval__in=[1,2,3])
qs = qs.order_by(""-record__root_id"")
print(qs.query)
}}}
{{{
SELECT ""orion_twomodel"".""id"", ""orion_twomodel"".""record_id"", ""orion_twomodel"".""twoval"" FROM ""orion_twomodel"" INNER JOIN ""orion_onemodel"" ON (""orion_twomodel"".""record_id"" = ""orion_onemodel"".""id"") LEFT OUTER JOIN ""orion_onemodel"" T3 ON (""orion_onemodel"".""root_id"" = T3.""id"") WHERE ""orion_onemodel"".""oneval"" IN (1, 2, 3) ORDER BY T3.""id"" ASC
}}}
One gets the query with the two JOINs but an ASCENDING sort order.  I was not under the impression that sort orders are somehow relative to the class level sort order, eg: does specifing `order_by(""-record__root_id"")` invert the class sort order?  Testing that on a simple case doesn't show that behavior at all.

Thanks for any assistance and clarification.
"	Bug	closed	Database layer (models, ORM)	3.1	Normal	fixed			Accepted	1	0	0	0	0	0