Order by a join alias in sqlalchemy
<p>I have used manual join (Query.join not joinedload) in sqlalchemy for some reason. I have used alias since I have multiple join to a single table. Now I want to sort the result by one of relations' fields. How can I use Query.order_by with aliased name? When I do this, I get a ambiguous %(####) instead of field name in query.</p>

<pre><code>        if self.order_by:
        entity = self.cls
        for field, order in self.order_by:

            if '.' in field:

                m = re.match(r'(.+)\.(.+)', field)

                if m.group(1) not in self.aliases:

                    for item in m.group(1).split('.'):

                        cls = inspect(entity)
                        attr = cls.attrs[item]
                        entity = get_type(attr)

                        if attr.innerjoin:
                            aliased_entity = aliased(entity)
                            self.aliases[m.group(1)] = aliased_entity
                            _query = _query.join(aliased_entity, item).options(contains_eager(item,
                                                                                        alias=aliased_entity))
                        else:
                            aliased_entity = aliased(entity)
                            self.aliases[m.group(1)] = aliased_entity
                            _query = _query.outerjoin(aliased_entity, item).options(contains_eager(item,
                                                                                                 alias=aliased_entity))

            if order == "desc":
                _query = _query.order_by(self.get_order_by_field(field).desc())
            else:
                _query = _query.order_by(self.get_order_by_field(field).asc())
</code></pre>

<p>And then:</p>

<pre><code>    def get_order_by_field(self, field: str) -&gt; Column:
        if '.' in field:
            m = re.match(r'(.+)\.(.+)', field)
            if m.group(1) in self.aliases:
                return getattr(self.aliases[m.group(1)], m.group(2))
            else:
                return Column(self.column_map[field])
        else:
            return Column(field)
</code></pre>