CODEWITHSUNDEEP
pythonpython-3.xsqlalchemy
maslak
maslak

Reputation: 1255

How to list child occurences count and parent field

I would like to make a query which will return a list containing parent.name's and number of clicks for each of the name. Result may look something like this:

[
    ('parent_name1', 35),
    ('parent_name2', 99),
    ....
]

My current approach:

res_list = []

    parents = session.query(Parent).filter(Parent.grandparent_id == some_grandparent_id).all()

    for parent in parents:
        counts = session.query(Child).filter(Child.parent_id == parent.id).count()
        res_list.append((parent.name, counts))

Is there better approach for this? Something sql specific?

My models:

class Click(Model):
    __tablename__ = 'click'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('Parent.id'))

class Parent(Model):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    name = Column(String(20))
    grandparent_id = Column(Integer, ForeignKey('grandparent.id'))

class GrandParent(Model):
    __tablename__ = 'grandparent'

    id = Column(Integer, primary_key=True)
    name = Column(String(20))

Thanks in advance!

Upvotes: 0

Views: 74

Answers (1)

Mehrdad Pedramfar
Mehrdad Pedramfar

Reputation: 11073

Try this:

from sqlalchemy import func

result = session.query(Parent.name, func.count(Click.parent_id)).group_by(Click.parent_id).having(Parent.grandparent_id == some_grandparent_id).all()

or:

from sqlalchemy import func

result = session.query(Parent.name, func.count(Click.parent_id)).join(Click).group_by(Click.parent_id).having(Parent.grandparent_id == some_grandparent_id).all()

Upvotes: 1

Related Questions