Description and expected behavior
Problem : Grouping by only computed fields produces the error "dbErrorCode":"42803" coming from the PostgreSQL.
Why this happen : Because the generated query does not use computed field aliases specified in the select statement, but expand them once again in the group by clause.
Solution : replacing the computed field expansion by their aliases in the group by clause solves the problem
Exemple:
- ZenStack orm: 3.4.4
- Database type: Postgresql
model User {
id Int @id @default(autoincrement())
birthdate DateTime
year Int @computed
yearQuarter Int @computed
}
const result = await db.user.groupBy({
by: ["year", "yearQuarter"],
_min: { value: true },
_max: { value: true },
});
produces :
select
EXTRACT(YEAR FROM "birthdate") as "year",
ceil(EXTRACT(MONTH FROM "birthdate") / 3) as "yearQuarter",
min("value") as "_min.value",
max("value") as "_max.value"
from "public"."Users" as "User"
where (
[...]
)
group by EXTRACT(YEAR FROM "birthdate"), ceil(EXTRACT(MONTH FROM "birthdate") / 3) --this throws on PostgreSQL
--group by "year", "yearQuarter" --this runs smooth
Description and expected behavior
Problem : Grouping by only computed fields produces the error "dbErrorCode":"42803" coming from the PostgreSQL.
Why this happen : Because the generated query does not use computed field aliases specified in the select statement, but expand them once again in the group by clause.
Solution : replacing the computed field expansion by their aliases in the group by clause solves the problem
Exemple:
produces :