Querying the same table more than once in SQLAlchemy

Question

I'm using SQLAlchemy in Pyramid application and have the following pair of tables.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id          = Column(Integer, primary_key=True)
    name        = Column(String, nullable=False, default='', unique=True)
    manager     = Column(Integer, nullable=False, default=-1)

class MyStats(Base):
    __tablename__ = 'my_stats'
    id          = Column(Integer, primary_key=True)
    agent       = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    manager     = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)

    kpi_one     = Column(Integer)
    kpi_two     = Column(Integer)

MyStats stores the manager of the agent at the time (so we can look at how their performance changed based on who their manager was or independently of their manager as we like).

I want to run a query where I get the following:

Agent  Manager  KPI1   KPI2
Bob    Fred     1      1.1
Alice  Owain    1.2    0.9

And so on and so forth. Now pulling it up with just the Agent name or just the manager name I can do:

DBSession.query(User.name, MyStats.kpi_one, MyStats.kpi_two).filter(User.id == MyStats.agent)

What I can't do is query for both at the same time. I've spent the better part of my day trying to work this one out but can't.

Answer 1

You need to create aliased tables:

from sqlalchemy.orm import aliased

agent = aliased(User, name='agent')
manager = aliased(User, name='manager')
DBSession.query(
        agent.name, manager.name, MyStats.kpi_one, MyStats.kpi_two
    ).filter(
        agent.id == MyStats.agent,
        manager.id == MyStats.manager
    )