CODEWITHSUNDEEP
pythonormsqlalchemy
Tom Leung
Tom Leung

Reputation: 374

How to count child table items with or without join to parent table using SQLAlchemy?

I used SQLAlchemy to create a SQLite database which stores bibliographic data of some document, and I want to query the author number of each document.

I know how to do this in raw SQL, but how can I achieve the same result using SQLAlchemy? It is possible without using join?

Here is the classes that I have defined:


    class WosDocument(Base):
        __tablename__ = 'wos_document'

        document_id = Column(Integer, primary_key=True)
        unique_id = Column(String, unique=True)
        ......
        authors = relationship('WosAuthor', back_populates='document')

    class WosAuthor(Base):
        __tablename__ = 'wos_author'

        author_id = Column(Integer, primary_key=True, autoincrement=True)

        document_unique_id = Column(String, ForeignKey('wos_document.unique_id'))
        document = relationship('WosDocument', back_populates='authors')

        last_name = Column(String)
        first_name = Column(String)

And my goal is to get the same result as this SQL query does:


     SELECT a.unique_id, COUNT(*) 
     FROM wos_document AS a 
     LEFT JOIN wos_author AS b 
     ON a.unique_id = b.document_unique_id 
     GROUP BY a.unique_id

I tried the codes below:


    session.query(WosDocument.unique_id, len(WosDocument.authors)).all()

    session.query(WosDocument.unique_id, func.count(WosDocument.authors)).all()

The first line raised an error, the second line doesn't give me the desired result, it return only one row and I don't recognize what it is:

[('000275510800023', 40685268)]

Since WosDocument Object has a one-to-many relationship authors, I supposed that I can query the author number of each document without using join explicitly, but I can't find out how to do this with SQLAlchemy.

Can you help me? Thanks!

Upvotes: 2

Views: 1965

Answers (1)

Shihe Zhang
Shihe Zhang

Reputation: 2771

If you have written the right relation in your model. Then the query would be like:

db.session.query(ParentTable.pk,func.count('*').label("count")).join(Childtable).group_by(ParentTable).all()

The detail of the document of the join() is https://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.join

If you don't join() explictly you would need to deal with something like parent.relations as a field.

Upvotes: 2

Related Questions