Name: How to Write a SQL Subquery with Drizzle ORM
Rating: 1.9 (5741 reviews)
Author: gitdagray

I need to write a SQL select query that joins a couple of tables.

No problem.

However, I also need to join that query with the results of another query.

This is possible in SQL with a subquery.

I am using Drizzle ORM to write type-safe queries instead of just raw SQL statements.

I create the main query BEFORE adding in the subquery:

import { db } from "@/index"
import { plant, genus } from "@/schema"
import { eq, or, like } from "drizzle-orm"

const plantData = await db.select({
        ID: plant.plantVarietyInfoId,
        Genus: genus.description,
}).from(plant)
        .leftJoin(genus, eq(plant.genusId, genus.id))
        .where(or(
            like(plant.plantVarietyInfoId, `%${searchTerm}%`),
            like(genus.description, `%${searchTerm}%`),
        ))
        .orderBy(genus.description)

Next, I need to add in a max ship week value from a Shipping Records table. This table has a many-to-many relationship with the Plant table.

To begin, I create the ship week subquery above the main query in the file:

import { db } from "@/index"
import { plant, genus } from "@/schema"
import { eq, or, like, max } from "drizzle-orm"

const shipWeekQuery = db.select({
        ShipWeek: max(shippingRecord.expectedShipWeek).as('shipWeek'),
        ID: shippingRecord.plantVarietyInfoId,
    }).from(shippingRecord)
        .groupBy(shippingRecord.plantVarietyInfoId)
        .as('shipWeekRecords')

I need to use as() for aliases in the above subquery twice. Once on the max ship week value, and once on the overall subquery. Without these, the subquery will not work.

When adding the subquery ShipWeek value to the main query, I need to refer to the shipWeekQuery.

Finally, I can add the subquery to the main query with a left join:

const plantData = await db.select({
        ID: plant.plantVarietyInfoId,
        Genus: genus.description,
        ShipWeek: shipWeekQuery.ShipWeek,
}).from(plant)
        .leftJoin(genus, eq(plant.genusId, genus.id))
        .leftJoin(shipWeekQuery, eq(plant.plantVarietyInfoId, shipWeekQuery.ID))
        .where(or(
            like(plant.plantVarietyInfoId, `%${searchTerm}%`),
            like(genus.description, `%${searchTerm}%`),
        ))
        .orderBy(genus.description)

You can read more about creating a select from subquery in the Drizzle ORM docs.

Let's Connect!

Hi, I'm Dave. I work as a full-time developer, instructor and creator.

If you enjoyed this article, you might enjoy my other content, too.

My Stuff: Courses, Cheat Sheets, Roadmaps

My Blog: davegray.codes

YouTube: @davegrayteachescode

X: @yesdavidgray

GitHub: gitdagray

LinkedIn: /in/davidagray

Patreon: Join my Support Team!

Buy Me A Coffee: You will have my sincere gratitude

Thank you for joining me on this journey.

Dave