select_related with multiple ForeignKey to same table generates sub-optimal SQL

[djbug]

I have this model where Relation has 2 foreign keys, both to Node.

class Node(models.Model):
    name = models.CharField(max_length=25)

class Relation(models.Model):
    left = models.ForeignKey(Node)
    right = models.ForeignKey(Node, related_name='right')

I've shown a sample query along with the SQL that's generated by Django:

Relation.objects.select_related().filter(id=1)

SELECT "relation"."id", "relation"."left_id", "relation"."right_id", "left"."id", "left"."name", T3."id", T3."name", 
FROM "relation" INNER JOIN "left" ON ( "relation"."left_id" = "left"."id" ) INNER JOIN "right" T3 ON ("relation"."right_id" = T3."id" ) 
WHERE "relation"."id" = 1

There are 2 INNER JOIN happening above. However we can do with just 1 INNER JOIN, since left & right are pointing to the same table. See below:

SELECT "relation"."id", "relation"."left_id", "relation"."right_id", "left"."id", "left"."name", 
FROM "relation" INNER JOIN "left" ON ( "relation"."left_id" = "left"."id" OR "relation"."right_id" = "left"."id")
WHERE "relation"."id" = 1

P.S. Not sure if this is related : [7125]

[Anssi Kääriäinen]

I don't think that actually works. Assume the following on node table:

id	name
1	node 1
2	node 2

And relation has one model with left_id = 1 and right_id = 2. Now, the second query produces results

id	left_id	right_id	node.id	name
1	1	2	1	node 1
1	1	2	2	node 2

Which is not the wanted result. Instead we want all the related objects in a single row. Trying to collapse the two rows into a single object while iterating leads to all sorts of really complex issues which we are not going to tackle.