Drizzle TeamDT
Drizzle Team•2y ago
Caspian Nightworth

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


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));


I am stuck on how to handle the INNER JOIN in my original SQL
Solution
That helped. Thank you
Jump to solution
Was this page helpful?