Joins being dropped when specifying multiple fields in `select_related`

[joshsharp]

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.author

What 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.

[collerek]

Hi,

This project seems stale and from what I saw on encode page it's not a priority right now. Since I was tired with reinventing the wheel and needed something as soon as possible I created ormar package, that was inspired by this one.

Ormar bases its validation on pydantic so it can be used directly with fastapi as response and request models.

This issue was implemented in ormar, so feel free to check it out: https://github.com/collerek/ormar