pypika icon indicating copy to clipboard operation
pypika copied to clipboard

Subquery isn't surround in params of aggregate function

Open magiskboy opened this issue 5 years ago • 0 comments

Recently, I use aggregate functions and I passed a subquery to an aggregate function and it should be surround because Postgres does not allow that

Example:

from pypika import Query, Tables
from pypika.terms import AggregateFunction


parent, child = Tables('parent', 'child')

childrent_subquery = Query.from_(child).select(
    AggregateFunction(
        'json_agg',
        AggregateFunction(
            'json_build_object',
            'name', child.name,
            'age', child.age,
        ))
).where(child.parent_id == parent.id)

main_query = Query.from_(parent).select(
    AggregateFunction(
        'json_agg',
        AggregateFunction(
            'json_build_object',
            'name', parent.name,
            'childrent', childrent_subquery,
        )
    )
)

print(main_query.get_sql())

Actual

SELECT
  json_agg(json_build_object('name', "name", 'childrent',
      SELECT
        json_agg(json_build_object('name', "child"."name", 'age', "child"."age"))
        FROM "child"
      WHERE
        "child"."parent_id" = "parent"."id"))
FROM
  "parent"

Expect

SELECT
  json_agg(json_build_object('name', "name", 'childrent',
      (SELECT
        json_agg(json_build_object('name', "child"."name", 'age', "child"."age"))
        FROM "child"
      WHERE
        "child"."parent_id" = "parent"."id")))
FROM
  "parent"

magiskboy avatar Feb 04 '21 08:02 magiskboy