SQLAlchemy how to get unmapped COUNT(*) as a result to a query?

Question

I'm having trouble understanding how to get an unmapped column result (Such as COUNT(*)) to a mapped query.

I have two mapped tables like so:

class Actor(Base):
    __tablename__ = 'actor'
    id: int = Column(Integer)
    name: str = Column(String)

    movies = relationship("Movie", secondary=movie_actors_association_table, back_populates="actors")


class Movie(Base):
    __tablename__ = 'movie'
    id: int = Column(Integer)
    name: str = Column(String)
    year: int = Column(Integer)

    actors = relationship("Actor", secondary=movie_actors_association_table, back_populates="actors")
    

The movie_actors_association_table looks like this:

movie_actors_association_table = Table('movie_actors', Base.metadata,
                                       Column('movie_id', ForeignKey('movie.id'), primary_key=True),
                                       Column('actor_id', ForeignKey('actor.id'), primary_key=True)
                                       )

My goal is to print out the actors sorted by the number of movies they did in a certain year(or range of years) but I also want to get the count in the result. For example, if in 1999 Brad Pitt did 5 movies, Angelina Jolie did 3 and Nicolas Cage did 15 then I want the result to be something like (Nicolas Cage,15), (Brad Pitt,5), (Angelina Jolie,3) for 1999.

For this, what I'm currently doing is:

current_filter = Movie.year == 1999
query_movies = Query(Movie).filter(current_filter)
query_movies_id = Query(Movie.id).filter(current_filter)


def query_to_txt_stmt(query):
    """converts Query object to SQL text statement"""
    stmt = query.statement
    stmt_complied = stmt.compile(dialect=sqlite.dialect(), compile_kwargs={"literal_binds": True})
    print(stmt_complied)
    return stmt_complied

stmt = text(
    f"SELECT actor.*, COUNT(*) as my_count FROM "
    f"actor JOIN movie_actors on actor.id = movie_actors.actor_id "
    f"WHERE movie_actors.movie_id IN ({query_to_txt_stmt(query_movies_id)}) "
    f"GROUP BY actor.id ORDER BY my_count DESC"
    )


final_query = Query(Actor).from_statement(stmt)
final_query.session = db.session # the session created in an offscreen db class
results = final_query.all() #query executes here, but I get all Actor objects as a result. Without the count.

This approach gives me the correct sorting of the actors, but I don't get the actual COUNT in the output. And I can't figure out how to add it to the results. And this is the heart of my question. How do I add an un-mapped column to the results? And is it possible to map the COUNT result onto the mapped Actor class in a temporary variable for example?

Note: I'm using the raw text query because I need to pass around the Query object as a parameter without it being attached to a session.But when I tried doing something like this: (Like it's suggested in this answer.)

query = Query(Actor, func.count(Actor.id).label("my_count")).join(movie_actors_association_table,movie_actors_association_table.c.actor_id == Actor.id).group_by(Actor.id).order_by(text("my_count DESC"))

And then giving it the session like so:

query.session = db.session
query.all()

It would fail to run the query because the SQL it generated did not include the custom label of my_count. So it said "No Such Column 'my_count'"

However, if I did it on the session like so:

db.session.query(Actor, func.count(Actor.id).label("my_count")).join(movie_actors_association_table,movie_actors_association_table.c.actor_id == Actor.id).group_by(Actor.id).order_by(text("my_count DESC"))

So if I don't use the raw text, it seems that I have to have access to the session when writing the query. And I don't want that. If someone has an idea how to not use raw text here and still maintain independence from the session I'll be happy to hear about it.

Answer 1

you can try this

db.session.query(Actor).join(movie_actors_association_table,movie_actors_association_table.c.actor_id == Actor.id)
.with_entities(Actor.id, func.count(movie_actors_association_table.c.actor_id).label("my_count"))
.group_by(Actor.id)
.order_by(desc(literal_column("my_count")))