Marcus - noxify Comments - Answer Overflow

Marcus - noxify

Posts Comments

DTDrizzle Team

•Created by shahidcodes on 4/13/2024 in #help

Fetching many to many along with some aggregation using select

Thanks Freddie for adding it to your repo ❤️ https://github.com/fredericoo/drizzle-toolbelt

16 replies

DTDrizzle Team

•Created by shahidcodes on 4/13/2024 in #help

Fetching many to many along with some aggregation using select

  /**
   * database response is something like:
   * [
   *   { user: { id: 1, name: 'user1' }, group: { id: 1, name: 'group1' } },
   *   { user: { id: 1, name: 'user1' }, group: { id: 2, name: 'group2' } },
   *   { user: { id: 2, name: 'user2' }, group: { id: 1, name: 'group1' } }
   * ]
   * 
   * references:
   * - https://orm.drizzle.team/docs/joins#left-join
   * - https://orm.drizzle.team/docs/rqb#many-to-many
   */
  const rows = await db
    .select({
      user: getTableColumns(users),
      group: getTableColumns(groups),
    })
    .from(users)
    .leftJoin(usersToGroups, eq(usersToGroups.userId, users.id))
    .leftJoin(groups, eq(usersToGroups.groupId, groups.id));

  /**
   * reduce result is something like:
   * {
   *   '1': { user: { id: 1, name: 'user1' }, groups: [ [Object], [Object] ] },
   *   '2': { user: { id: 2, name: 'user2' }, groups: [ [Object] ] }
   * }
   * 
   * references:
   * - https://orm.drizzle.team/docs/joins#aggregating-results
   */
  const result = rows.reduce<Record<number, { user: User; groups: Group[] }>>(
    (acc, row) => {
      const user = row.user;
      const group = row.group;
      if (!acc[user!.id]) {
        acc[user!.id] = { user: user!, groups: [] };
      }
      if (group) {
        acc[user!.id].groups.push(group);
      }
      return acc;
    },
    {}
  );

  /**
   * transformed response is something like:
   * [
   *   { id: 1, name: 'user1', groups: [ [Object], [Object] ] },
   *   { id: 2, name: 'user2', groups: [ [Object] ] }
   * ]
   */
  const transformedResponse = Object.values(result).map((ele) => ({
    ...ele.user,
    groups: ele.groups,
  }));

  /**
   * database response is something like:
   * [
   *   { user: { id: 1, name: 'user1' }, group: { id: 1, name: 'group1' } },
   *   { user: { id: 1, name: 'user1' }, group: { id: 2, name: 'group2' } },
   *   { user: { id: 2, name: 'user2' }, group: { id: 1, name: 'group1' } }
   * ]
   * 
   * references:
   * - https://orm.drizzle.team/docs/joins#left-join
   * - https://orm.drizzle.team/docs/rqb#many-to-many
   */
  const rows = await db
    .select({
      user: getTableColumns(users),
      group: getTableColumns(groups),
    })
    .from(users)
    .leftJoin(usersToGroups, eq(usersToGroups.userId, users.id))
    .leftJoin(groups, eq(usersToGroups.groupId, groups.id));

  /**
   * reduce result is something like:
   * {
   *   '1': { user: { id: 1, name: 'user1' }, groups: [ [Object], [Object] ] },
   *   '2': { user: { id: 2, name: 'user2' }, groups: [ [Object] ] }
   * }
   * 
   * references:
   * - https://orm.drizzle.team/docs/joins#aggregating-results
   */
  const result = rows.reduce<Record<number, { user: User; groups: Group[] }>>(
    (acc, row) => {
      const user = row.user;
      const group = row.group;
      if (!acc[user!.id]) {
        acc[user!.id] = { user: user!, groups: [] };
      }
      if (group) {
        acc[user!.id].groups.push(group);
      }
      return acc;
    },
    {}
  );

  /**
   * transformed response is something like:
   * [
   *   { id: 1, name: 'user1', groups: [ [Object], [Object] ] },
   *   { id: 2, name: 'user2', groups: [ [Object] ] }
   * ]
   */
  const transformedResponse = Object.values(result).map((ele) => ({
    ...ele.user,
    groups: ele.groups,
  }));

Checked the snippet from freddie after writing the snippet and it seems that his solution is more generic and reusable.

16 replies

DTDrizzle Team

•Created by shahidcodes on 4/13/2024 in #help

Fetching many to many along with some aggregation using select

Question: Whats the reason for the fullJoin instead of leftJoin ?

16 replies

DTDrizzle Team

•Created by shahidcodes on 4/13/2024 in #help

Fetching many to many along with some aggregation using select

or something else - found this thread while testing many 2 many relations and currently testing your provided solution.

16 replies

DTDrizzle Team

•Created by shahidcodes on 4/13/2024 in #help

Fetching many to many along with some aggregation using select

As alternative to lodash.get => https://youmightnotneed.com/lodash#get

16 replies

KKysely

•Created by Marcus - noxify on 4/12/2024 in #help

Complex Query Builder from dynamic input - Typescript issue

Hi @Igal - just want you to know that everything works now. I just changed the as any to as ExpressionWrapper<KyselyDatabase<TableDefinition>, "tablename", SqlBool> Thanks again for spending your free time to solve this challenge. Let's see if I can find something more challenging 😉

6 replies

KKysely

•Created by Marcus - noxify on 4/12/2024 in #help

Complex Query Builder from dynamic input - Typescript issue

Thanks igal - will give it a try

6 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

hope this is not bad practices

15 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

selectFields are "type-safe" and you can only select the fields which are defined in the provided table definition

15 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

the test shows how I want to implement it later in the graphql resolver

15 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

Updated the code and made it a bit generic:

// ---- query builder

/**
 * Special thanks goes to https://github.com/igalklebanov
 * for solving our edge case issue with multiple schematas <3
 * https://www.answeroverflow.com/m/1227929774144618598
 */
import type { SelectExpression } from "kysely"
import {
  sql,
  DummyDriver,
  Kysely,
  PostgresAdapter,
  PostgresIntrospector,
  PostgresQueryCompiler,
} from "kysely"

export const initDb = <T>() => {
  return new Kysely<T>({
    dialect: {
      createAdapter() {
        return new PostgresAdapter()
      },
      createDriver() {
        return new DummyDriver()
      },
      createIntrospector(db: Kysely<T>) {
        return new PostgresIntrospector(db)
      },
      createQueryCompiler() {
        return new PostgresQueryCompiler()
      },
    },
  })
}

interface KyselyDatabase<TableDefinition> {
  tablename: TableDefinition
}

interface GenerateQueryProps<TableDefinition> {
  catalog: string
  schema: string
  table: string
  selectFields: SelectExpression<
    KyselyDatabase<TableDefinition>,
    keyof KyselyDatabase<TableDefinition>
  >[]
}

export function generateQuery<TableDefinition>(
  props: GenerateQueryProps<TableDefinition>,
) {
  const db = initDb<KyselyDatabase<TableDefinition>>()

  const query = db
    .selectFrom(
      sql.id(
        props.catalog,
        props.schema,
        props.table,
      ) as unknown as "tablename",
    )
    .select(props.selectFields)

  return query.compile()
}


// ---- vitest

import { describe, test } from "vitest"
import { generateQuery } from "../src"

interface DummyTableDefinition {
  stringField: string
  numberField: number
  boolField: boolean
}

describe("Query Builder", () => {
  test("simple", () => {
    const query = generateQuery<DummyTableDefinition>({
      catalog: "hive",
      schema: "tier1_dummy_lake",
      table: "dummy_table",
      selectFields: ["stringField"],
    })
  })
})

// ---- query builder

/**
 * Special thanks goes to https://github.com/igalklebanov
 * for solving our edge case issue with multiple schematas <3
 * https://www.answeroverflow.com/m/1227929774144618598
 */
import type { SelectExpression } from "kysely"
import {
  sql,
  DummyDriver,
  Kysely,
  PostgresAdapter,
  PostgresIntrospector,
  PostgresQueryCompiler,
} from "kysely"

export const initDb = <T>() => {
  return new Kysely<T>({
    dialect: {
      createAdapter() {
        return new PostgresAdapter()
      },
      createDriver() {
        return new DummyDriver()
      },
      createIntrospector(db: Kysely<T>) {
        return new PostgresIntrospector(db)
      },
      createQueryCompiler() {
        return new PostgresQueryCompiler()
      },
    },
  })
}

interface KyselyDatabase<TableDefinition> {
  tablename: TableDefinition
}

interface GenerateQueryProps<TableDefinition> {
  catalog: string
  schema: string
  table: string
  selectFields: SelectExpression<
    KyselyDatabase<TableDefinition>,
    keyof KyselyDatabase<TableDefinition>
  >[]
}

export function generateQuery<TableDefinition>(
  props: GenerateQueryProps<TableDefinition>,
) {
  const db = initDb<KyselyDatabase<TableDefinition>>()

  const query = db
    .selectFrom(
      sql.id(
        props.catalog,
        props.schema,
        props.table,
      ) as unknown as "tablename",
    )
    .select(props.selectFields)

  return query.compile()
}


// ---- vitest

import { describe, test } from "vitest"
import { generateQuery } from "../src"

interface DummyTableDefinition {
  stringField: string
  numberField: number
  boolField: boolean
}

describe("Query Builder", () => {
  test("simple", () => {
    const query = generateQuery<DummyTableDefinition>({
      catalog: "hive",
      schema: "tier1_dummy_lake",
      table: "dummy_table",
      selectFields: ["stringField"],
    })
  })
})

15 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

15 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

this works perfect! Thanks for the quick help

15 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

Will try the second approach since my current eslint setup will throw an error if there is an any (using the t3 turbo stack)

15 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

Hi, thanks for the quick response - will give it a try

15 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

setting the types seems to work: https://kyse.link/fDkOU Question: Is this the "way to go" ? Note: it seems I missed something in the type definition, because I have now access to all fields from all tables ( tested it locally with two tables )

15 replies

KKysely

•Created by Marcus - noxify on 4/11/2024 in #help

How to use multiple schema definitions ( e.g. <catalog>.<schema>.<table> )

Have seen, that kysely provides a sql helper ( sql.id ). Via:

  const test = db
  .selectFrom(sql.id("catalog_name", "schema_name", "person"))
  .selectAll();

  const test = db
  .selectFrom(sql.id("catalog_name", "schema_name", "person"))
  .selectAll();

I get the correct SELECT statement, but also some type errors 😕 Playground: https://kyse.link/sV7ww

15 replies

TTCTheo's Typesafe Cult

•Created by Vlatko on 1/17/2024 in #questions

What library do you guys use for API's?

For graphql I use graphql-yoga with pothos - currently using „node:http“ as „server“ - works fine in our case (deployed to k8s)

6 replies

TTCTheo's Typesafe Cult

•Created by Vlatko on 1/17/2024 in #questions

What library do you guys use for API's?

Is there a standard? Maybe https://github.com/honojs/hono could be a solution for your next project?

6 replies

Gaming

Programming