[Sqlalchemy-tickets] Issue #4274: join Query.from_statement (zzzeek/sqlalchemy)

[dima-starosud <iss...@bi...>]

New issue 4274: join Query.from_statement
https://bitbucket.org/zzzeek/sqlalchemy/issues/4274/join-queryfrom_statement

dima-starosud:

This is using SQLAlchemy=1.2.8.

I am trying to join two models by relationship, but I want to emulate values of second model using `from_statement`.

```
#!python
Base = declarative_base()


class X(Base):
    __tablename__ = 'x'
    id = Column(Integer, primary_key=True)


class Y(Base):
    __tablename__ = 'y'
    id = Column(Integer, primary_key=True)
    x_id = Column(Integer, ForeignKey(X.id))
    x = relationship(X)


Base.metadata.create_all(session.bind)

session.add(Y(x=X()))
session.flush()

Y1 = aliased(Y, name='y1')
X1 = Query(X).from_statement(select([label('id', literal(5))])).subquery(name='x1')
session.query(Y1).join(X1, Y.x).all()

```

Traceback:
```
Traceback (most recent call last):
  File "join_test.py", line 49, in <module>
    session.query(Y1).join(X1, Y.x).all()
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2090, in join
    from_joinpoint=from_joinpoint)
  File "<string>", line 2, in _join
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/base.py", line 201, in generate
    fn(self, *args[1:], **kw)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2234, in _join
    outerjoin, full, create_aliases, prop)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2295, in _join_left_to_right
    prop, overlap)
  File "/usr/local/lib/python3.5/site-packages/sqlalchemy/orm/query.py", line 2339, in _prepare_right_side
    right_mapper.mapped_table.description))
sqlalchemy.exc.InvalidRequestError: Selectable 'x1' is not derived from 'x'
```

The error message is pretty clear. But I would like to know whether it's possible to achieve what I wanted? Maybe I just misused the API?

Thanks in advance!