DEV Community

Dave Gray
Dave Gray

Posted on • Originally published at davegray.codes on

How to Write a SQL Subquery with Drizzle ORM

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)
Enter fullscreen mode Exit fullscreen mode

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')
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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

Image of AssemblyAI tool

Transforming Interviews into Publishable Stories with AssemblyAI

Insightview is a modern web application that streamlines the interview workflow for journalists. By leveraging AssemblyAI's LeMUR and Universal-2 technology, it transforms raw interview recordings into structured, actionable content, dramatically reducing the time from recording to publication.

Key Features:
🎥 Audio/video file upload with real-time preview
🗣️ Advanced transcription with speaker identification
⭐ Automatic highlight extraction of key moments
✍️ AI-powered article draft generation
📤 Export interview's subtitles in VTT format

Read full post

Top comments (0)

Billboard image

Imagine monitoring that's actually built for developers

Join Vercel, CrowdStrike, and thousands of other teams that trust Checkly to streamline monitor creation and configuration with Monitoring as Code.

Start Monitoring

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay