Count of O2M relations with condition

Question

My question will be linked with SQL, Python and SQLAlchemy
I've got 3 models in database:

Department
----------
id: int
name: varchar

Employee
--------
id: int
name: varchar
email: varchar
department: FK to Depratment table

Report
------
id: int
message: varchar
created_by: FK to Employee
approved: boolean

How can I query the count of Employees by each department who has at least one approved Report?

I'm using async session to PostgreSQL and I've tried to query it by next query:

SELECT department.name, COUNT(department.name)
FROM employee
JOIN department ON department.id = employee.department_id
JOIN report ON report.created_by = employee.id
WHERE report.approved
GROUP BY department.name

But I got the count multiplied by number of employee's reports. For example: 1 department with 1 employee with 10 reports and one of this reports was approved.

def get_data(session):
    query = (
        session.query(
            Department.name,
            funcfilter(func.count(Department.name), Report.approved),
        )
        .select_from(Employee)
        .join(Employee.department)
        .join(Employee.reports)
    )
    return session.execute(query).all()

async with get_session(read_only=True) as session:
    result = await session.run_sync(get_data)

Actual result:

  Department.name | count
-------------------------
      Sales       | 10

I expect:

  Department.name | count
-------------------------
      Sales       | 1

Count of O2M relations with condition

0 Answers0