ericmartinezr Posts - Answer Overflow

ericmartinezr

•Created by ericmartinezr on 12/8/2023 in #help

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.

4 replies

DTDrizzle Team

•Created by ericmartinezr on 11/29/2023 in #help

Return array when parsing a schema with drizzle-valibot

Hello guys, I just started using drizzle-valibot and I'm not able to find in their docs nor yours how to return an array when parsing a schema

// Schema
export const facturaciones = mysqlTable('...', {});
export type NuevaFacturacion = typeof facturaciones.$inferInsert;
export const nuevaFacturacionSchema = createInsertSchema(facturaciones);

// Controller
// Frontend can send many 'facturaciones', I want to parse each one of them
async guardar(@Body() facturaciones: NuevaFacturacion[]) {
  // This doesn't return an array
  const _facturaciones = parse(nuevaFacturacionSchema, facturaciones);
}

// Schema
export const facturaciones = mysqlTable('...', {});
export type NuevaFacturacion = typeof facturaciones.$inferInsert;
export const nuevaFacturacionSchema = createInsertSchema(facturaciones);

// Controller
// Frontend can send many 'facturaciones', I want to parse each one of them
async guardar(@Body() facturaciones: NuevaFacturacion[]) {
  // This doesn't return an array
  const _facturaciones = parse(nuevaFacturacionSchema, facturaciones);
}

Or do I have to iterate over the array and parse each item by myself?
Thanks in advance!
PS: I tried to add drizzle-valibot as a tag but it doesn't exist

2 replies

DTDrizzle Team

•Created by ericmartinezr on 11/20/2023 in #help

How to use inner join with rqb?

Hello there! Having the following schema

export const proyectos = mysqlTable('proyecto', {
  codigo: int('codigo').primaryKey()
});

export const plataformas = mysqlTable(
  'plataforma',
  {
    codigo: int('codigo').notNull(),
    plataforma: int('plataforma').notNull()
  },
  table => {
    return {
      pk: primaryKey({ columns: [table.codigo, table.plataforma] })
    };
  }
);

export const plataformaMantenedor = mysqlTable('plataforma_mantenedor', {
  id: int('id').primaryKey(),
  nombre: varchar('nombre', { length: 25 }).notNull()
});

export const proyectosRelations = relations(proyectos, ({ many }) => ({
  plataformas: many(plataformas)
}));

export const plataformaRelations = relations(plataformas, ({ one }) => ({
  proyectos: one(proyectos, {
    fields: [plataformas.codigo],
    references: [proyectos.codigo]
  }),
  plataformaMantenedor: one(plataformaMantenedor, {
    fields: [plataformas.plataforma],
    references: [plataformaMantenedor.id]
  })
}));

export const proyectos = mysqlTable('proyecto', {
  codigo: int('codigo').primaryKey()
});

export const plataformas = mysqlTable(
  'plataforma',
  {
    codigo: int('codigo').notNull(),
    plataforma: int('plataforma').notNull()
  },
  table => {
    return {
      pk: primaryKey({ columns: [table.codigo, table.plataforma] })
    };
  }
);

export const plataformaMantenedor = mysqlTable('plataforma_mantenedor', {
  id: int('id').primaryKey(),
  nombre: varchar('nombre', { length: 25 }).notNull()
});

export const proyectosRelations = relations(proyectos, ({ many }) => ({
  plataformas: many(plataformas)
}));

export const plataformaRelations = relations(plataformas, ({ one }) => ({
  proyectos: one(proyectos, {
    fields: [plataformas.codigo],
    references: [proyectos.codigo]
  }),
  plataformaMantenedor: one(plataformaMantenedor, {
    fields: [plataformas.plataforma],
    references: [plataformaMantenedor.id]
  })
}));

This query gives me all the "proyectos" when I actually want only those that match "plataformas" with id (aka plataforma) "[10,12]" because drizzle is using LEFT JOIN to join the tables.

let s = this.db.query.proyectos
      .findMany({
        with: {
          plataformas: {
            with: {
              plataformaMantenedor: true
            },
            where: _ => inArray(plataformas.plataforma, [10, 12])
          }
        },

        limit: 25,
        offset: 0
      })

let s = this.db.query.proyectos
      .findMany({
        with: {
          plataformas: {
            with: {
              plataformaMantenedor: true
            },
            where: _ => inArray(plataformas.plataforma, [10, 12])
          }
        },

        limit: 25,
        offset: 0
      })

Is there a way to use "inner join" to filter all the rows with the inner where? I'm still learning drizzle so I may be making a mistake anywhere else. I hope I made myself clear enough (english is not my main language) Thanks in advance!

11 replies

DTDrizzle Team

•Created by ericmartinezr on 11/18/2023 in #help

Relational and sql-like query apis in the same file

Hello! My first question here. Drizzle looks really cool and I'm trying to learn it. I have this scenario where I have my schema file like this

// schema.ts
export const proyectos = mysqlTable('my_table', { ... });

// schema.ts
export const proyectos = mysqlTable('my_table', { ... });

Then I use it in my service file

import * as schema from 'schema';

this.db.query.proyectos.findMany(...);

import * as schema from 'schema';

this.db.query.proyectos.findMany(...);

This works just fine but I also have the other kind of query type

this.db.select().from(schema.proyectos)...

this.db.select().from(schema.proyectos)...

But I don't like having to add schema. everytime so I specify in the import what I want exactly

import { proyectos } from 'schema'
this.db.select().from(proyectos)...

import { proyectos } from 'schema'
this.db.select().from(proyectos)...

This works fine but then the next stops working and it doesn't recognize it anymore

// rip
this.db.query.proyectos.findMany()... // It doesn't find 'proyectos' in 'query' anymore.

// rip
this.db.query.proyectos.findMany()... // It doesn't find 'proyectos' in 'query' anymore.

Is there a way to have both kind of queries in the same file without doing anything special?

6 replies

Gaming

Programming