hunt Posts - Answer Overflow

hunt

•Created by hunt on 5/2/2024 in #help

Help building queries based on multiple conditions from an array of objects

Hello, I'm having trouble formulating a query that will match rows to multiple conditions at the same time. Suppose I have an array of objects like :

const arr = [ 
    { param1 : "val1", param2 : "val2" }, 
    { param2 : "val3", param2 : "val4" }
  ]

const arr = [ 
    { param1 : "val1", param2 : "val2" }, 
    { param2 : "val3", param2 : "val4" }
  ]

and a Table1 with :

col1 : VarChar
col2 : VarChar

col1 : VarChar
col2 : VarChar

where param1 should match up with col1, and param2 should match with col2. In this example, how can I query Table1 so that I only retrieve rows where (col1 == "val1" && col2 == "val2") or (col1 == "val3" && col2 == "val4") ? If I extract all the values of param1 and param2, and use them each in an inArray, like :

const param1s = arr.map(x => x.param1)
const param2s = arr.map(x => x.param2)

select().from(table1).where(and(inArray(table1.col1, param1s), inArray(table1.col2, param2s)))

const param1s = arr.map(x => x.param1)
const param2s = arr.map(x => x.param2)

select().from(table1).where(and(inArray(table1.col1, param1s), inArray(table1.col2, param2s)))

I run the risk of also returning values such as col1 == "val1" col2 == "val4" and col1 == "val3" col2 == "val2" which were not in the original array. Is there a way to do this? Thank you

2 replies

DTDrizzle Team

•Created by hunt on 3/21/2024 in #help

Emulate upsert operation in mySQL

Hi guys, I'm a little new to drizzle / SQL. Is the following method a good way of generically emulating an upsert for a MySQL db? Are there any issues with this strategy?

import { Table, getTableColumns, sql } from "db";
/**
 *
 * Helper to get the SQL "set" value for 'onDuplicateKeyUpdate' for any given table.
 *
 */
export const getUpsertValues = (table: Table) => {
  const col_names = getTableColumns(table);

  type ResType = {
    [K in keyof typeof col_names]: any;
  };
  let res: ResType = {};

  Object.keys(col_names).map((col_name) => {
    const column = col_names[col_name];

    res[col_name] = sql`VALUES(${column.name})`
      ? sql`VALUES(${column.name})`
      : sql`${column.name}`;
  });

  return res;
};




// Insert operation that behaves like an upsert 

const table = someDrizzleMySqlTable
await db
      .insert(table)
      .values(valuesToUpsert)
      .onDuplicateKeyUpdate({
          set : {...getUpsertValues(table)}
        })

import { Table, getTableColumns, sql } from "db";
/**
 *
 * Helper to get the SQL "set" value for 'onDuplicateKeyUpdate' for any given table.
 *
 */
export const getUpsertValues = (table: Table) => {
  const col_names = getTableColumns(table);

  type ResType = {
    [K in keyof typeof col_names]: any;
  };
  let res: ResType = {};

  Object.keys(col_names).map((col_name) => {
    const column = col_names[col_name];

    res[col_name] = sql`VALUES(${column.name})`
      ? sql`VALUES(${column.name})`
      : sql`${column.name}`;
  });

  return res;
};




// Insert operation that behaves like an upsert 

const table = someDrizzleMySqlTable
await db
      .insert(table)
      .values(valuesToUpsert)
      .onDuplicateKeyUpdate({
          set : {...getUpsertValues(table)}
        })

Thanks

1 replies

Gaming

Programming