Pagination for aggregate queries

[jameshod5]

Context: We have moved to using fastapi.paginate for the requests now, which is straight forward for most requests and provides cursor based pagination for our responses. This paginate function only needs the database and the query. The function needs to return a CursorPage[model] object.

Problem: When trying to do cursor pagination for aggregate queries however, it returns an error about columns being missing based on what the aggregate query is asking for:

mast-api | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "min_shot_id" does not exist
mast-api | LINE 1: ..._id) AS min_shot_id, max(shot_id) AS max_shot_id, min_shot_i...
mast-api | ^
mast-api |
mast-api | [SQL: SELECT min(shot_id) AS min_shot_id, max(shot_id) AS max_shot_id, min_shot_id AS _sqlakeyset_oc_3
mast-api | FROM shots ORDER BY _sqlakeyset_oc_3 DESC
mast-api | LIMIT %(param_1)s]
mast-api | [parameters: {'param_1': 51}]
mast-api | (Background on this error at: https://sqlalche.me/e/14/f405)

Current attempts: The first attempt was to create a model based on what we should expect from the response, however creating this dummy model did not fix the issue.

How to reproduce:

• Using the alternate aggregate function for shots:

@app.get("/json/shots/aggregate")
def get_shots_aggregate(
    request: Request,
    response: Response,
    db: Session = Depends(get_db),
    params: AggregateQueryParams = Depends(),
) -> CursorPage[AggModel]:
    
    query = crud.aggregate_query(
        ShotModel, params.data, params.groupby, params.filters, params.sort
    )
    return paginate(db, query)

• Use the request: http://localhost:8081/json/shots/aggregate?data=shot_id$min:,shot_id$max:&groupby=campaign&sort=-min_shot_id