Caspian Nightworth Posts - Answer Overflow

Caspian Nightworth

Explore posts from servers

DIAdiscord.js - Imagine a boo! 👻SIASapphire - Imagine a framework TTCTheo's Typesafe Cult DTDrizzle Team

•Created by Caspian Nightworth on 6/11/2024 in #help

Convert PostgreSQL to Drizzle ORM

I have the following SQL that I am trying to convert to Drizzle ORM for my NextJS project.

SELECT
  ffxiv_blue_mage_tracker_spells.id,
  ffxiv_blue_mage_tracker_spells.name,
  ffxiv_blue_mage_tracker_spells.description,
  ffxiv_blue_mage_tracker_spells.tooltip,
  ffxiv_blue_mage_tracker_spells.patch,
  ffxiv_blue_mage_tracker_spells.icon,
  ffxiv_blue_mage_tracker_spells.type,
  ffxiv_blue_mage_tracker_spells.aspect,
  (
    SELECT
      STRING_AGG(source_text, ', ')
    FROM
      ffxiv_blue_mage_tracker_sources AS src
    WHERE
      src.spell_id = ffxiv_blue_mage_tracker_spells.id
  ) AS where_to_aquire
FROM
  ffxiv_blue_mage_tracker_spells
  INNER JOIN (
    SELECT
      spell_id,
      -- Remove grouping by source_text
      STRING_AGG(source_text, ', ') AS source_text
    FROM
      ffxiv_blue_mage_tracker_sources
    GROUP BY
      spell_id
  ) AS ffxiv_blue_mage_tracker_sources ON ffxiv_blue_mage_tracker_spells.id = ffxiv_blue_mage_tracker_sources.spell_id
ORDER BY
  ffxiv_blue_mage_tracker_spells.id ASC

SELECT
  ffxiv_blue_mage_tracker_spells.id,
  ffxiv_blue_mage_tracker_spells.name,
  ffxiv_blue_mage_tracker_spells.description,
  ffxiv_blue_mage_tracker_spells.tooltip,
  ffxiv_blue_mage_tracker_spells.patch,
  ffxiv_blue_mage_tracker_spells.icon,
  ffxiv_blue_mage_tracker_spells.type,
  ffxiv_blue_mage_tracker_spells.aspect,
  (
    SELECT
      STRING_AGG(source_text, ', ')
    FROM
      ffxiv_blue_mage_tracker_sources AS src
    WHERE
      src.spell_id = ffxiv_blue_mage_tracker_spells.id
  ) AS where_to_aquire
FROM
  ffxiv_blue_mage_tracker_spells
  INNER JOIN (
    SELECT
      spell_id,
      -- Remove grouping by source_text
      STRING_AGG(source_text, ', ') AS source_text
    FROM
      ffxiv_blue_mage_tracker_sources
    GROUP BY
      spell_id
  ) AS ffxiv_blue_mage_tracker_sources ON ffxiv_blue_mage_tracker_spells.id = ffxiv_blue_mage_tracker_sources.spell_id
ORDER BY
  ffxiv_blue_mage_tracker_spells.id ASC

So far what I have is the following and I'm not entirely sure that what I have so far is even correct:

const spellList = await db
    .select({
      spellId: spells.id,
      name: spells.name,
      description: spells.description,
      tooltip: spells.tooltip,
      patch: spells.patch,
      icon: spells.icon,
      type: spells.type,
      aspect: spells.aspect,
      whereToAquire: sql<string>`select string_agg(${sources.sourceText}, ', ') from ${sources} as src where src.spell_id = ${spells.id}`,
    })
    .from(spells)
    .innerJoin(sources, eq(spells.id, sources.spellId))
    .orderBy(asc(spells.id));

const spellList = await db
    .select({
      spellId: spells.id,
      name: spells.name,
      description: spells.description,
      tooltip: spells.tooltip,
      patch: spells.patch,
      icon: spells.icon,
      type: spells.type,
      aspect: spells.aspect,
      whereToAquire: sql<string>`select string_agg(${sources.sourceText}, ', ') from ${sources} as src where src.spell_id = ${spells.id}`,
    })
    .from(spells)
    .innerJoin(sources, eq(spells.id, sources.spellId))
    .orderBy(asc(spells.id));

I am stuck on how to handle the INNER JOIN in my original SQL

4 replies

DTDrizzle Team

•Created by Caspian Nightworth on 6/10/2024 in #help

conflicting peer dependency when installing drizzle-orm

I am trying to install drizzle orm in a fresh install of Nextjs and have the following error presented

❯ npm i drizzle-orm
npm error code ERESOLVE
npm error ERESOLVE could not resolve
npm error
npm error While resolving: [email protected]
npm error Found: [email protected]
npm error node_modules/react
npm error   peer react@"^18.2.0" from [email protected]
npm error   node_modules/next
npm error     next@"14.2.3" from the root project
npm error   peer react@"^18.3.1" from [email protected]
npm error   node_modules/react-dom
npm error     peer react-dom@"^18.2.0" from [email protected]
npm error     node_modules/next
npm error       next@"14.2.3" from the root project
npm error     react-dom@"^18" from the root project
npm error   2 more (styled-jsx, the root project)
npm error
npm error Could not resolve dependency:
npm error drizzle-orm@"*" from the root project
npm error
npm error Conflicting peer dependency: [email protected]
npm error node_modules/react
npm error   peer react@"18.2.0" from [email protected]
npm error   node_modules/react-native
npm error     peer react-native@">0.73.0" from @op-engineering/[email protected]
npm error     node_modules/@op-engineering/op-sqlite
npm error       peerOptional @op-engineering/op-sqlite@">=2" from [email protected]
npm error       node_modules/drizzle-orm
npm error         drizzle-orm@"*" from the root project
npm error
npm error Fix the upstream dependency conflict, or retry
npm error this command with --force or --legacy-peer-deps
npm error to accept an incorrect (and potentially broken) dependency resolution.

❯ npm i drizzle-orm
npm error code ERESOLVE
npm error ERESOLVE could not resolve
npm error
npm error While resolving: [email protected]
npm error Found: [email protected]
npm error node_modules/react
npm error   peer react@"^18.2.0" from [email protected]
npm error   node_modules/next
npm error     next@"14.2.3" from the root project
npm error   peer react@"^18.3.1" from [email protected]
npm error   node_modules/react-dom
npm error     peer react-dom@"^18.2.0" from [email protected]
npm error     node_modules/next
npm error       next@"14.2.3" from the root project
npm error     react-dom@"^18" from the root project
npm error   2 more (styled-jsx, the root project)
npm error
npm error Could not resolve dependency:
npm error drizzle-orm@"*" from the root project
npm error
npm error Conflicting peer dependency: [email protected]
npm error node_modules/react
npm error   peer react@"18.2.0" from [email protected]
npm error   node_modules/react-native
npm error     peer react-native@">0.73.0" from @op-engineering/[email protected]
npm error     node_modules/@op-engineering/op-sqlite
npm error       peerOptional @op-engineering/op-sqlite@">=2" from [email protected]
npm error       node_modules/drizzle-orm
npm error         drizzle-orm@"*" from the root project
npm error
npm error Fix the upstream dependency conflict, or retry
npm error this command with --force or --legacy-peer-deps
npm error to accept an incorrect (and potentially broken) dependency resolution.

4 replies

Gaming

Programming