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

Question

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!

I think the relationship is wrong, try something like. document = db.relationship( 'Wos', primaryjoin='WosAuther.document_unique_id == WosDocument.document_id', backref='authers') you can query document, and it would have a list contain many wosauther — Shihe Zhang
@ShiheZhang Thanks. I can query a single document's authors by wosdocument.authors with my relationship, but I don't know how to query the author number count of each document. — Tom Leung
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 This query result puzzle me. if some document have no author the count(*) would still have 1.Are you sure that's what you want? — Shihe Zhang
@ShiheZhang every document must have at least one author, even sometimes the author is anonymous (I have checked that). And len(wosdocument.authors) will raise an error if I put it into session.query(). But the len() function works fine if I already fetched a document object — Tom Leung

Shihe Zhang Shihe Zhang · Accepted Answer · 2019-01-26T08:54:34

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.

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

1 Answers