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

Solution:

That helped. Thank you

Jump to solution

2 Replies

Sillvva•6mo ago

This might work: https://orm.drizzle.team/docs/select#select-from-subquery

const sqSources = db
  .select({
    spellId: sources.spellId,
    sourceText: sql<string>`string_agg(${sources.sourceText}, ', ')`.as('source_text')
  })
  .groupBy(sources.spellId)
  .as('sources');

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: sqSources.sourceText,
  })
  .from(spells)
  .innerJoin(sqSources, eq(spells.id, sqSources.spellId))
  .orderBy(asc(spells.id));

const sqSources = db
  .select({
    spellId: sources.spellId,
    sourceText: sql<string>`string_agg(${sources.sourceText}, ', ')`.as('source_text')
  })
  .groupBy(sources.spellId)
  .as('sources');

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: sqSources.sourceText,
  })
  .from(spells)
  .innerJoin(sqSources, eq(spells.id, sqSources.spellId))
  .orderBy(asc(spells.id));

And if you want all columns from spells, you can also do this:

const spellList = await db
  .select({
    ...getTableColumns(spells),
    whereToAquire: sqSources.sourceText,
  })
  .from(spells)
  .innerJoin(sqSources, eq(spells.id, sqSources.spellId))
  .orderBy(asc(spells.id));

const spellList = await db
  .select({
    ...getTableColumns(spells),
    whereToAquire: sqSources.sourceText,
  })
  .from(spells)
  .innerJoin(sqSources, eq(spells.id, sqSources.spellId))
  .orderBy(asc(spells.id));

Drizzle ORM - Select

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Solution

Caspian Nightworth•6mo ago

That helped. Thank you

Gaming

Programming

Convert PostgreSQL to Drizzle ORM