Drizzle Team•12mo ago

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 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        TRUE

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        TRUE

The 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.

3 Replies

ericmartinezrOP•12mo ago

I ran out of characters lol... This works just fine if the relation is one-to-one but with many-to-one it fails. With one-to-one the query it yields is this one:

SELECT    `table1`.`id`,
          `table1_table2`.`data` AS `table2`
FROM      `table1`
LEFT JOIN lateral
          (
                 SELECT json_array(`table1_table2`.`id`, `table1_table2`.`id_tbl1`, `table1_table2`.`my_number`, sum(`table1_table2`.`my_number`) over(partition BY `table1_table2`.`id`)) AS `data`
                 FROM   (
                               SELECT *
                               FROM   `table2` `table1_table2`
                               WHERE  `table1_table2`.`id_tbl1` = `table1`.`id`
                               LIMIT  1) `table1_table2`) `table1_table2`
ON        TRUE

SELECT    `table1`.`id`,
          `table1_table2`.`data` AS `table2`
FROM      `table1`
LEFT JOIN lateral
          (
                 SELECT json_array(`table1_table2`.`id`, `table1_table2`.`id_tbl1`, `table1_table2`.`my_number`, sum(`table1_table2`.`my_number`) over(partition BY `table1_table2`.`id`)) AS `data`
                 FROM   (
                               SELECT *
                               FROM   `table2` `table1_table2`
                               WHERE  `table1_table2`.`id_tbl1` = `table1`.`id`
                               LIMIT  1) `table1_table2`) `table1_table2`
ON        TRUE

I noticed that with many-to-one it adds a COALESCE and a JSON_ARRAYAGG to the query. This is a sample of my real code which is a query a lot bigger, I reduced it to the minimum I could.

Andrii Sherman•12mo ago

Relational Query API is a bit limited for now, so not all such functions may work in extra field

ericmartinezrOP•12mo ago

I ended up using a CTE and a subquery

Gaming

Programming

Window function fails with many-to-one relationship