Window function fails with many-to-one relationship
Hello guys,
I'm trying to use a window function to sum some values with a many-to-one relations but it's failing with this error
With the following query
The schema is as follows:
And this is the query I'm trying to execute
Hopefully I was clear enough and I appreciate your help in advance.
I'm trying to use a window function to sum some values with a many-to-one relations but it's failing with this error
You cannot use the window function 'sum' in this context.You cannot use the window function 'sum' in this context.With the following query
SELECT `table1`.`id`,
`table1_table2`.`data` AS `table2`
FROM `table1`
LEFT JOIN lateral
(
SELECT coalesce(json_arrayagg(json_array(`table1_table2`. `id`, `table1_table2`.`id_tbl1`, `table1_table2`.`my_number`, sum(`table1_table2`.`my_number`) over(partition BY `table1_table2`.`id`))), json_array()) AS `data`
FROM `table2` `table1_table2`
WHERE `table1_table2`.`id_tbl1` = `table1`.`id`) `table1_table2`
ON TRUESELECT `table1`.`id`,
`table1_table2`.`data` AS `table2`
FROM `table1`
LEFT JOIN lateral
(
SELECT coalesce(json_arrayagg(json_array(`table1_table2`. `id`, `table1_table2`.`id_tbl1`, `table1_table2`.`my_number`, sum(`table1_table2`.`my_number`) over(partition BY `table1_table2`.`id`))), json_array()) AS `data`
FROM `table2` `table1_table2`
WHERE `table1_table2`.`id_tbl1` = `table1`.`id`) `table1_table2`
ON TRUEThe schema is as follows:
export const table1 = mysqlTable('table1', {
id: int('id').primaryKey().autoincrement()
});
export const table2 = mysqlTable('table2', {
id: int('id').primaryKey().autoincrement(),
table1Id: int('id_tbl1').notNull(),
myNumber: int('my_number')
});
export const table1Relations = relations(table1, ({one, many}) => ({
table2: many(table2),
}));
export const table2Relations = relations(table2, ({one}) => ({
table1: one(table1, {
fields: [table2.table1Id],
references: [table1.id]
})
}));export const table1 = mysqlTable('table1', {
id: int('id').primaryKey().autoincrement()
});
export const table2 = mysqlTable('table2', {
id: int('id').primaryKey().autoincrement(),
table1Id: int('id_tbl1').notNull(),
myNumber: int('my_number')
});
export const table1Relations = relations(table1, ({one, many}) => ({
table2: many(table2),
}));
export const table2Relations = relations(table2, ({one}) => ({
table1: one(table1, {
fields: [table2.table1Id],
references: [table1.id]
})
}));And this is the query I'm trying to execute
const qb = db.query.table1.findMany({
with: {
table2: {
extras: {
sumNumber: sql<number>`SUM(${schema.table2.myNumber}) OVER(partition by ${schema.table2.id})`.as('sumNumber')
}
}
}
}).prepare().execute();const qb = db.query.table1.findMany({
with: {
table2: {
extras: {
sumNumber: sql<number>`SUM(${schema.table2.myNumber}) OVER(partition by ${schema.table2.id})`.as('sumNumber')
}
}
}
}).prepare().execute();Hopefully I was clear enough and I appreciate your help in advance.
