Aggregating joined tables in SQLAlchemy

user_8430792 0
Viewed 232

I got this aggregate function working in Django ORM, it counts some values and percents from the big queryset and returns the resulting dictionary.

queryset = Game.objects.prefetch_related(
    "timestamp",
    "fighters",
    "score",
    "coefs",
    "rounds",
    "rounds_view",
    "rounds_view_f",
    "finishes",
    "rounds_time",
    "round_time",
    "time_coef",
    "totals",
).all()

values = queryset.aggregate(
    first_win_cnt=Count("score", filter=Q(score__first_score=5)),
    min_time_avg=Avg("round_time__min_time"),
    # and so on
) # -> dict

I'm trying to achieve the same using SQLAlchemy and this is my tries so far:

q = (
    db.query(
        models.Game,
        func.count(models.Score.first_score)
        .filter(models.Score.first_score == 5)
        .label("first_win_cnt"),
    )
    .join(models.Game.fighters)
    .filter_by(**fighter_options)
    .join(models.Game.timestamp)
    .join(
        models.Game.coefs,
        models.Game.rounds,
        models.Game.rounds_view,
        models.Game.rounds_view_f,
        models.Game.finishes,
        models.Game.score,
        models.Game.rounds_time,
        models.Game.round_time,
        models.Game.time_coef,
        models.Game.totals,
    )
    .options(
        contains_eager(models.Game.fighters),
        contains_eager(models.Game.timestamp),
        contains_eager(models.Game.coefs),
        contains_eager(models.Game.rounds),
        contains_eager(models.Game.rounds_view),
        contains_eager(models.Game.rounds_view_f),
        contains_eager(models.Game.finishes),
        contains_eager(models.Game.score),
        contains_eager(models.Game.rounds_time),
        contains_eager(models.Game.round_time),
        contains_eager(models.Game.time_coef),
        contains_eager(models.Game.totals),
    )
    .all()
)

And it gives me an error:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.GroupingError) column "stats_fighters.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT stats_fighters.id AS stats_fighters_id, stats_fighter...

I don't really understand why there should be stats_fighters.id in the group by, and why do I need to use group by. Please help.

This is the SQL which generates Django ORM:

SELECT 
   AVG("stats_roundtime"."min_time") AS "min_time_avg",
   COUNT("stats_score"."id") FILTER (
      WHERE "stats_score"."first_score" = 5) AS "first_win_cnt"
     FROM "stats_game" LEFT OUTER JOIN "stats_roundtime" ON ("stats_game"."id" = "stats_roundtime"."game_id") 
    LEFT OUTER JOIN "stats_score" ON ("stats_game"."id" = "stats_score"."game_id")
sqlalchemy django-orm sql python
2 Answers

Group by is used in connection with rows that have the same values and you want to calculate a summary. It is often used with sum, max, min or average.

Since SQLAlchemy generates the final SQL command you need to know your table structure and need to find out how to make SQLAlchemy to generate the right SQL command.

Doku says there is a group_by method in SQLAlchemy.

May be this code might help.

q = (
    db.query(
        models.Game,
        func.count(models.Score.first_score)
        .filter(models.Score.first_score == 5)
        .label("first_win_cnt"),
    )
    .join(models.Game.fighters)
    .filter_by(**fighter_options)
    .join(models.Game.timestamp)
    .group_by(models.Game.fighters)
    .join(
       models.Game.coefs,
       models.Game.rounds,
       models.Game.rounds_view,
       models.Game.rounds_view_f,
       models.Game.finishes,
       models.Game.score,
       models.Game.rounds_time,
       models.Game.round_time,
       models.Game.time_coef,
       models.Game.totals,
   )
user_67486720

func.count is an aggregation function. If any expression in your SELECT clause uses an aggregation, then all expressions in the SELECT must be constant, aggregation, or appear in the GROUP BY.

if you try SELECT a,max(b) the SQL parser would complain that a is not an aggregation or in group by. In your case, you may consider adding models.Game to GROUP BY.

user_105496620
Related