How to add count of items to a FastAPI API using SQLAlchemy and Pydantic?

Question

I have three SQLAlchemy models, let's say a 'project' table, a 'worker' table and an 'equipment' table. I am using the Pydantic Schemas.

workers and equipment can be assigned to a project through Foreignkeys (i.e. worker.project_id ==project.id).

In my API end point of projects, in addition to project information, I want to give the number (count) of workers and equipment assigned to the project.

How should I do this, while I don't have a Column for these counts in my 'project' table, and hence in my Model to pass them?

class project(Base):
    __tablename__ = "projects"
    id = Column(Integer, primary_key=True)
    name = Column(String(80), nullable=False)
    site_name = Column(String(80), nullable=False)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable = False)


class worker(Base):
    __tablename__ = "workers"
    id = Column(Integer, primary_key=True)
    fullname = Column(String(80), nullable=False)
    project_id = Column(Integer, ForeignKey('projects.id')


class equipment(Base):
    __tablename__ = "equipments"
    id = Column(Integer, primary_key=True)
    name = Column(String(80), nullable=False)
    project_id = Column(Integer, ForeignKey('projects.id')

---------------------------------------------------------------
router = APIRouter(tags=['Projects'])

@router.get('/projects/', response_model=List[ProjectSchema])
def all_projects(db:Session=Depends(get_db)):
    return project_fn.get_all(db)

-----------------------------------------------
project_fn.py

def get_all(db:Session=Depends(get_db)):
    projects = db.query(project).all()
    return projects

-----------------------------------------------
class ProjectSchema(BaseModel):
    id: int
    name: str
    site_name: str = Field(min_length=3)
    company: CompanyBase

    class Config:
        orm_mode = True

----------------------------------------------
Sample API I need:

{
    "id": 1,
    "name": "Project-01",
    "site_name": "Site-01",
    "company": {
      "id": 1,
      "name": "Company-01",
    }
    "worker_count" : 5,
    "equipment_count" : 3
}

How to get 'worker_count' and 'equipment_count' here?

You can use `func.count` to get group-by-count for related columns; you might have to massage the data slightly to get the structure you want (i.e. instead of `(Project, WorkerCount, EquipmentCount)` since you want everything inside the `project` object). See https://stackoverflow.com/questions/25500904/counting-relationships-in-sqlalchemy for how to do the SQLAlchemy part. — MatsLindh, Dec 31 '21 at 22:12
@MatsLindh Thanks for your feedback, but I am still not clear what to do. Can I use `func.count` in my 'project' model to query other tables? I can easily get the counts by a query in 'worker' or 'equipment' models, but the main problem is how to pass them into 'project' API. Does creating a secondary table help here, as used in your mentioned link? because apparently I cannot add 'worker_id' and 'equipment_id' to my 'project' table, since these are many to many relationships. — Reza-sh, Dec 31 '21 at 23:05
You didn't include `project_fn`, so it's hard to say how you're querying the db, but `session.query(project, func.count(worker.id), func.count(equipment.id)).join(worker, isouter=True).join(equipment, isouter=True).all()` could work, I think it'd give you a tuple with three entries; the project, the workercount and the equipment count — MatsLindh, Jan 01 '22 at 00:19
@MatsLindh I added project_fn and Pydantic schema. Maybe I am missing something obvious here, but I assume that when we adhere to SQLAlchemy and Pydantic models, we can only pass existing fields in these models to our API. And in SQLAlchemy I assume we can have only 'Columns' or 'relationships' as a field, and not any other custom field? Am I right here? — Reza-sh, Jan 01 '22 at 14:05
Did you try to modify your query to include the two funcs as I included in my comment, together with the explicit joins? It should give you a three element tuple back with project and counts. — MatsLindh, Jan 01 '22 at 21:29
@MatsLindh I tried it in a number of ways. First I got the error that `column "projects.id" must appear in the GROUP BY clause or be used in an aggregate function`, so I added `.group_by(project.id)` . Then I got the validation errors from Pydantic, so I removed the response model for now. Now I get the API, but without the counts. Actually it is not giving a tuple, only list of projects, as if the `func.count` s are not there?! — Reza-sh, Jan 01 '22 at 22:45

score 0 · Answer 1 · edited Jan 10 '22 at 01:12

Ok, I found the answer as below and used column_property to access other tables and count the required items in project model:

class project(Base):
    __tablename__ = "projects"
    id = Column(Integer, primary_key=True)
    name = Column(String(80), nullable=False)
    site_name = Column(String(80), nullable=False)
    company_id = Column(Integer, ForeignKey('companies.id'), nullable = False)

    worker_count = column_property(select([func.count(worker.id)]).filter(worker.project_id==id).scalar_subquery())

    equipment_count = column_property(select([func.count(equipment.id)]).filter(equipment.project_id==id).scalar_subquery())

How to add count of items to a FastAPI API using SQLAlchemy and Pydantic?

1 Answers1