I have a raw inner join query with counting, written directly on Postgres SQL:
SELECT "films"."id" AS "megaId",
COUNT("filmComments"."id") AS "numOfComments"
FROM "films"
INNER JOIN "filmComments"
ON ("films"."id" = "filmComments"."filmId")
GROUP BY "films"."id";
How can I make the same, using normal SqlAlchemy, without connection.execute(sqlCode)
?
P.S. My SqlAlchemy table classes:
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
from sqlalchemy import Column, Integer, String, Date, Float
class Film(Base):
__tablename__ = "films"
id = Column(Integer, primary_key = True)
name = Column(String)
rating = Column(Float)
marksCount = Column(Integer)
commentsCount = Column(Integer, index=True)
class FilmComment(Base):
__tablename__ = "filmComments"
id = Column(Integer, primary_key = True)
filmId = Column(Integer, index=True)
rating = Column(Integer, index=True)
text = Column(String)
votesUp = Column(Integer)
votesDown = Column(Integer)
userId = Column(Integer)
date = Column(Date)
Python Flask and SQLAlchemy ORM Effect of joining is achieved by just placing two tables in either the columns clause or the where clause of the select() construct. Now we use the join() and outerjoin() methods. The join() method returns a join object from one table object to another.
Mapping that to SQLAlchemy should be quite straightforward. I'm not considering the aliases, for obvious reasons.
from sqlalchemy import func
megaId, numOfComments = (session.query(Film.id, func.count(FilmComment.id))
.join(FilmComment, Film.id == FilmComment.filmId)
.group_by(Film.id).first())
This should work. The explicit on
clause wouldn't be needed if FilmComment.filmId
were declared as a foreign key.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With