ericmartinezr Comments - Answer Overflow

ericmartinezr

Posts Comments

DTDrizzle Team

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

Window function fails with many-to-one relationship

I ended up using a CTE and a subquery

4 replies

DTDrizzle Team

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

Window function fails with many-to-one relationship

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.

4 replies

DTDrizzle Team

•Created by Dev Emm on 12/5/2023 in #help

Increment without first selecting

You can do

db.update(table)
.set({
  columnName: sql`${tableName.columnName} + 1`
})

db.update(table)
.set({
  columnName: sql`${tableName.columnName} + 1`
})

3 replies

DTDrizzle Team

•Created by Angel on 11/30/2023 in #help

Type for select with relations?

How would you go with the ReturnType utility? I can't find anything about it in the docs

7 replies

DTDrizzle Team

•Created by Angel on 11/30/2023 in #help

Type for select with relations?

I would also like to know how to achieve this. Thanks for the workaround btw.

7 replies

DTDrizzle Team

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

Return array when parsing a schema with drizzle-valibot

Answering myself

const _facturaciones = parse(array(nuevaFacturacionSchema), facturaciones);

const _facturaciones = parse(array(nuevaFacturacionSchema), facturaciones);

Sorry about the noise

2 replies

DTDrizzle Team

•Created by bambam22 on 11/26/2023 in #help

Map casing when using sql`` operator

https://orm.drizzle.team/docs/sql#sqlmapwith probably this?

61 replies

DTDrizzle Team

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

How to use inner join with rqb?

Thanks a lot Angelelz!!

11 replies

DTDrizzle Team

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

How to use inner join with rqb?

Any idea about this one? Is my schema the problem or something else? Is it a bug? Something not yet covered by the drizzle? Am I misusing it?

11 replies

DTDrizzle Team

•Created by mono2712 on 11/21/2023 in #help

Dynamic Drizzle Relational Queries

I have something similar in my code, it may be useful

const table1Conditions: SQLWrapper[] = [];
const table2Conditions: SQLWrapper[] = [];

if (frontendFilter1) {
    table1Conditions.push( inArray(table1.field, [1,2,3]) );
}

if (frontendFilter2) {
    table2Conditions.push( eq(table2.field, frontendFilter2) );
}

db.query.table1.findMany({
    where: and(...table1Conditions),
    with {
        table2: {
            where: and(...table2Conditions)
        }
    }
});

const table1Conditions: SQLWrapper[] = [];
const table2Conditions: SQLWrapper[] = [];

if (frontendFilter1) {
    table1Conditions.push( inArray(table1.field, [1,2,3]) );
}

if (frontendFilter2) {
    table2Conditions.push( eq(table2.field, frontendFilter2) );
}

db.query.table1.findMany({
    where: and(...table1Conditions),
    with {
        table2: {
            where: and(...table2Conditions)
        }
    }
});

If the arrays are empty the where condition won't even show up so it's not a problem. Hopefully it makes sense.

8 replies

DTDrizzle Team

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

Relational and sql-like query apis in the same file

Thanks, that helps a lot!

6 replies

Gaming

Programming