I've been doing a lot of work in SQLAlchemy lately and I'm continually impressed with how easy it is for the basic things but also how it can also handle complicated items. One of the things I have had to do again and again is to have a complicated query with a lot of joins. I could fetch the top-items and the all the other rows and then all the other tables... but then I would have a lot of round trips and a mess of objects. I could use a session.query(Table1,Table2...).join(...) type of structure but then I wouldn't necessarily the object that I wanted. What I discovered is that you can map a SQLAlchemy subquery to an object, just like you do with tables. This is very much like a view in a database...only that you are doing all the work in SQLAlchemy, and not have to let the DBA know!

I have a contrived example on Github that shows how this work. The tables.py file defines the tables... there is a Person table, and Authors and Fans point to Person. Author's have Books and a Fan can have many Books (favorite_books). I wanted to create a Fandom query, which will show what each Fan likes (even if they like more than one! The query.py file shows that:

from tables import *
from sqlalchemy import select
from sqlalchemy.orm import aliased

FanPerson = aliased(Person, name="fan_person")
AuthorPerson = aliased(Person, name="author_person")

fandom_q = (
    select(
        Book.id,
        FanPerson.name.label("fan_name"),
        Book.title,
        AuthorPerson.name.label("author_name"),
    )
    .select_from(Fan)  # explicitly say where to start
    .join(FanPerson, Fan.person_id == FanPerson.id)  # Join to fan's person details
    .join(Fan.favorite_books)  # Join to books
    .join(Book.author)  # Join to author
    .join(
        AuthorPerson, Author.person_id == AuthorPerson.id
    )  # Join to author's person details
    .subquery()
)


class Fandom(Base):
    __table__ = fandom_q
    __mapper_args__ = {
        "primary_key": [Book.id, Fan.id]
    }  # using Book.id and Fan.id as the primary ensures we see all the rows

The fandom_q is the actual subquery and you can see that it isn't trivial. You have to make aliases for the Fans and the Authors to get their names and manually do joins. The SQLAlchemy-table object is the one that we are familiar with but note that it's slightly different -- instead of a __tablename__ attribute that takes the name of the table, we have a __table__ attribute that we set to the fandom_q subquery. We also set the primary key to make sure we get all the people back. You can download the project, install the deps with poetry and run the table_query.py file to see it in action (and play around with it.)

Now, in our code, we can use Fandom as table to do queries (ie filters in SqlAlchmey speak) on it, just like a normal table:

    with Session(engine) as session:
        for x in (
            session.query(Fandom).filter(Fandom.author_name == "Stephen King").all()
        ):
            print(x.fan_name, x.author_name, x.title)

So from an API perspective, the Fandom object is just another table that you query from but an insert, update, or delete operation because it's a select not a table. If you try one of those, you will get an error:

sqlalchemy.exc.ArgumentError: subject table for an INSERT, UPDATE or DELETE expected, got <sqlalchemy.sql.annotation.AnnotatedSubquery at 0x105babbb0; anon_1sqlalchemy.exc.ArgumentError: subject table for an INSERT, UPDATE or DELETE expected, got <sqlalchemy.sql.annotation.AnnotatedSubquery at 0x105babbb0; anon_1

Regardless, I find this way to use and re-use a long, complicated query really powerful, because I use all the power of a SQLAlchemy table object.