Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

An inner join with SqlAlchemy

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)
like image 623
Felix Avatar asked Nov 07 '13 16:11

Felix


People also ask

Do join in SQLAlchemy?

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.


1 Answers

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.

like image 66
Pedro Werneck Avatar answered Oct 14 '22 07:10

Pedro Werneck