Wappler Version : 5.2.2
Operating System : W10
Server Model: NodeJS
Database Type: Postgres
In a query, the main table has a left join, and a sub table column as well.
The column used from left join has a group function SUM attached.
In this scenario, the query fails to run with error saying column is not in grouping function.
Does the error only occur when the SUM is used in the sub table or also on the main table?
SUM is used in the main table. I haven’t tested by using SUM in sub table.
@patrick Had another use case.
Main table with nested column. In nested query builder, added a join, and selected a nested column from the joined table.
Inside this 2nd nested query builder, used an aggregate function.
Seeing two errors: UI shows 2nd nested column’s name as joined table itself, instead of the nested table. And when its run, getting the same error as above about grouping.
Could you turn on Debug on the query and tell me the output of the action, it should show the generated query for the main table.
Here’s the output with Debug on:
{
"qAddon": {
"options": {
"connection": "pg13",
"sql": {
"type": "select",
"columns": [
{
"table": "t_orders",
"column": "id"
},
{
"table": "t_orders",
"column": "order_name"
},
{
"table": "t_orders",
"column": "created_on"
},
{
"table": "t_order_details",
"column": "discount",
"alias": "total_discount",
"aggregate": "SUM"
},
{
"table": "t_order_details",
"column": "gross_amount",
"alias": "total_amt",
"aggregate": "SUM"
}
],
"table": {
"name": "t_orders"
},
"primary": "id",
"joins": [
{
"table": "t_order_details",
"column": "*",
"type": "INNER",
"clauses": {
"condition": "AND",
"rules": [
{
"table": "t_order_details",
"column": "order_id",
"operator": "equal",
"value": {
"table": "t_orders",
"column": "id"
},
"operation": "="
}
]
},
"primary": "id"
}
],
"query": "SELECT t_orders.id, t_orders.order_name, t_orders.created_on, SUM(t_order_details.discount) AS total_discount, SUM(t_order_details.gross_amount) AS total_amt\nFROM t_orders\nINNER JOIN t_order_details ON t_order_details.order_id = t_orders.id\nWHERE t_orders.created_on > '2022-10-11'\nGROUP BY t_orders.id, t_orders.order_name, t_orders.created_on",
"params": [],
"groupBy": [
{
"table": "t_orders",
"column": "id"
},
{
"table": "t_orders",
"column": "order_name"
},
{
"table": "t_orders",
"column": "created_on"
}
],
"wheres": {
"condition": "AND",
"rules": [
{
"id": "t_orders.created_on",
"field": "t_orders.created_on",
"type": "datetime",
"operator": "greater",
"value": "2022-10-11",
"data": {
"table": "t_orders",
"column": "created_on",
"type": "datetime",
"columnObj": {
"type": "timestamp",
"default": "now()",
"primary": false,
"unique": false,
"nullable": false,
"name": "created_on"
}
},
"operation": ">"
}
],
"conditional": null,
"valid": true
},
"sub": {
"state_changes": {
"type": "SELECT",
"table": {
"name": "t_order_state_changes",
"alias": "state_changes"
},
"key": "order_id",
"columns": [
{
"table": "state_changes",
"column": "state_id"
}
],
"primary": "id",
"joins": [],
"query": "SELECT state_id\nFROM t_order_state_changes AS state_changes",
"params": []
}
},
"sort": null,
"dir": "asc"
},
"test": true
},
"query": "[object Object]"
}
}
Getting error:
sql.toSQL is not a function
I’ve now noticed that the debug option was never correctly working, the sql object is not the knex instance which it should use. Here an update.
dbconnector.zip (2.1 KB)
Here’s the latest debug output.
The query still doesn’t work. @George
{
"qAddon": {
"options": {
"connection": "pg13",
"sql": {
"type": "select",
"columns": [
{
"table": "t_orders",
"column": "id"
},
{
"table": "t_orders",
"column": "order_name"
},
{
"table": "t_orders",
"column": "created_on"
},
{
"table": "t_order_details",
"column": "discount",
"alias": "total_discount",
"aggregate": "SUM"
},
{
"table": "t_order_details",
"column": "gross_amount",
"alias": "total_amt",
"aggregate": "SUM"
}
],
"table": {
"name": "t_orders"
},
"primary": "id",
"joins": [
{
"table": "t_order_details",
"column": "*",
"type": "INNER",
"clauses": {
"condition": "AND",
"rules": [
{
"table": "t_order_details",
"column": "order_id",
"operator": "equal",
"value": {
"table": "t_orders",
"column": "id"
},
"operation": "="
}
]
},
"primary": "id"
}
],
"query": "SELECT t_orders.id, t_orders.order_name, t_orders.created_on, SUM(t_order_details.discount) AS total_discount, SUM(t_order_details.gross_amount) AS total_amt\nFROM t_orders\nINNER JOIN t_order_details ON t_order_details.order_id = t_orders.id\nWHERE t_orders.created_on > '2022-10-11'\nGROUP BY t_orders.id, t_orders.order_name, t_orders.created_on\nORDER BY t_orders.id ASC",
"params": [
],
"groupBy": [
{
"table": "t_orders",
"column": "id"
},
{
"table": "t_orders",
"column": "order_name"
},
{
"table": "t_orders",
"column": "created_on"
}
],
"wheres": {
"condition": "AND",
"rules": [
{
"id": "t_orders.created_on",
"field": "t_orders.created_on",
"type": "datetime",
"operator": "greater",
"value": "2022-10-11",
"data": {
"table": "t_orders",
"column": "created_on",
"type": "datetime",
"columnObj": {
"type": "timestamp",
"default": "now()",
"primary": false,
"unique": false,
"nullable": false,
"name": "created_on"
}
},
"operation": ">",
"table": "t_orders"
}
],
"conditional": null,
"valid": true
},
"sub": {
"state_changes": {
"type": "SELECT",
"table": {
"name": "t_order_state_changes",
"alias": "state_changes"
},
"key": "order_id",
"columns": [
{
"table": "state_changes",
"column": "state_id"
}
],
"primary": "id",
"joins": [
],
"query": "SELECT state_id\nFROM t_order_state_changes AS state_changes",
"params": [
]
}
},
"orders": [
{
"table": "t_orders",
"column": "id",
"direction": "ASC"
}
],
"sort": null,
"dir": "asc"
},
"test": true
},
"query": {
"sql": "select \"t_orders\".\"id\", \"t_orders\".\"order_name\", \"t_orders\".\"created_on\", sum(\"t_order_details\".\"discount\") as \"total_discount\", sum(\"t_order_details\".\"gross_amount\") as \"total_amt\" from \"t_orders\" inner join \"t_order_details\" on \"t_order_details\".\"order_id\" = \"t_orders\".\"id\" where \"t_orders\".\"created_on\" > $1 group by \"t_orders\".\"id\", \"t_orders\".\"order_name\", \"t_orders\".\"created_on\" order by \"t_orders\".\"id\" ASC",
"bindings": [
"2022-10-11"
]
}
}
}
Does it still throw the same column is not in grouping function error?
Yes.
select "t_orders"."id", "t_orders"."order_name", "t_orders"."created_on", sum("t_order_details"."discount") as "total_discount", sum("t_order_details"."gross_amount") as "total_amt", "t_orders"."id" as "__dmxPrimary", "t_order_details"."id" as "__dmxPrimary_t_order_details" from "t_orders" inner join "t_order_details" on "t_order_details"."order_id" = "t_orders"."id" where "t_orders"."created_on" > $1 group by "t_orders"."id", "t_orders"."order_name", "t_orders"."created_on", "t_orders"."id" order by "t_orders"."id" ASC - column "t_order_details.id" must appear in the GROUP BY clause or be used in an aggregate function
Thanks that is more useful, for the sub table we do a separate query and we add some extra columns to the output that help us merge the data together. It seems that I fixed part of it but there is still a column left that needs to be added to the groupby clause. Will work on that now.
This seems to have done the trick. No more getting. Getting sub-table data array as well.
Haven’t checked the data-integrity, but will do that soon as well. For now, this is resolved for me.
Thanks 
.
Fixed in Wappler 5.2.3
