title: sqlalchemy async join
body: <p>I'm trying to implement a basic resource access using SQL Alchemy 1.4 and a PostgreSQL database.</p>
  <h3>Python code</h3>
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy.ext.declarative import DeclarativeMeta, declarative_base
  
  Base: DeclarativeMeta = declarative_base()
  
  
  class User(Base):
    __tablename__ = 'user'
  
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    email = Column(String(length=255), index=True, nullable=False)
  
  
  class Resource(Base):
    __tablename__ = &quot;resource&quot;
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String(length=255), index=True, nullable=False)
  
  
  class UserResourceRole(Base):
    __tablename__ = &quot;user_resource_role&quot;
  
    user_id = Column(
      UUID(as_uuid=True), 
      ForeignKey(&quot;user.id&quot;, ondelete=&quot;CASCADE&quot;),
      primary_key=True
    )
    resource_id = Column(
      UUID(as_uuid=True),
      ForeignKey(&quot;resource.id&quot;, ondelete=&quot;CASCADE&quot;),
      primary_key=True,
    )
    can_edit = Column(Boolean, default=False, nullable=False)
  </code></pre>
  <pre class="lang-py prettyprint-override"><code>from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
  from sqlalchemy.ext.asyncio.engine import AsyncEngine
  from sqlalchemy.orm import sessionmaker
  
  from the_other_file import User, Resource, UserResourceRole
  
  async def select_all(user: User, db_session: AsyncSession):
    results = await db_session.execute(
      select(Resource, UserResourceRole)
      .join(
        UserResourceRole,
        Resource.id == UserResourceRole.resource_id
      )
      .where(UserResourceRole.user_id == user.id)
    )
    return results.scalars().all()
  
  
  engine: AsyncEngine = create_async_engine(POSTGRES_URL, future=True)
  
  async_session = sessionmaker(
      bind=engine, class_=AsyncSession, expire_on_commit=False, future=True
  )
  
  
  # ignore the fact that it's not executed in asyncio loop
  a_db_session = await async_session()
  resources = await select_all(user=a_real_user_is_here, db_session=a_db_session)
  print(resources)
  </code></pre>
  <p>I can't retrieve anything from <code>UserResourceRole</code> in my results. It only contains data from <code>Resource</code>. If i swap the objects in the <code>select</code> call, then I can only retrieve data from <code>UserResourceRole</code>.</p>
  <h3>What I'm expecting</h3>
  <p>I'm expecting to have the same result of that SQL query :</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT *
  FROM resource
  INNER JOIN user_resource_role
  ON resource.id = user_resource_role.resource_id
  WHERE user_resource_role.user_id = :a_user_id
  </code></pre>
  <p>The query generated by SQL Alchemy is exactly the same (except the verbosity) :</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT resource.id, resource.name, user_resource_role.user_id, user_resource_role.resource_id, user_resource_role.can_edit
  FROM resource
  JOIN user_resource_role
  ON resource.id = user_resource_role.resource_id
  WHERE user_resource_role.user_id = :user_id_1
  </code></pre>