Description
Hello again. I have an issue with using multiple tables in select_related that results in the incorrect SQL being generated.
Using these tables as an example (irrelevant fields omitted):
class User(orm.Model, BaseUser):
pass
class Track(orm.Model):
pass
class Post(orm.Model):
track = orm.ForeignKey(Track, allow_null=True)
author = orm.ForeignKey(User)And using this query to illustrate:
posts = await Post.objects.select_related(['track','author']).all()With this query, some JOIN expressions are dropped from the resulting SQL, so it looks something like this:
SELECT [fields] FROM track, post JOIN "user" ON "user".id = post.authorWhat I'd expect to see is the track table also getting a JOIN expression, rather than just appearing in the FROM. This results in the incorrect behaviour of, as far as I can make out, joining every combination of track and post together. (I didn't even realise this was valid SQL, so I've learned something.)
I believe the issue is redefining select_from here, in models.py:
def build_select_expression(self):
tables = [self.table]
select_from = self.table
for item in self._select_related:
model_cls = self.model_cls
select_from = self.table # this is the culprit
for part in item.split("__"):
model_cls = model_cls.fields[part].to
select_from = sqlalchemy.sql.join(select_from, model_cls.__table__)
tables.append(model_cls.__table__)I think the core problem is the redefinition of select_from on line 76, as this stops the joins from being built up each time through the loop. So if that line is removed, it seems to solve the issue.