How to Write a SQL Subquery with Drizzle ORM
Tags: sql, drizzle, orm, mysql
Published: 2024-06-16 14:03:34

---
title: How to Write a SQL Subquery with Drizzle ORM
published: true
date: 2024-01-14 00:00:00 UTC
tags: sql,drizzle,orm,mysql
canonical_url: https://www.davegray.codes/posts/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](https://orm.drizzle.team/) to write type-safe queries instead of just raw SQL statements. 

I create the main query BEFORE adding in the subquery: 
```ts
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: 
```ts 
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: 
```ts 
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](https://orm.drizzle.team/docs/select#select-from-subquery) in the Drizzle ORM docs.

<hr />

## 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](https://courses.davegray.codes/)

**My Blog:** [davegray.codes](https://www.davegray.codes/)

**YouTube:**  [@davegrayteachescode](https://www.youtube.com/davegrayteachescode) 

**X:**  [@yesdavidgray](https://x.com/yesdavidgray)  

**GitHub:** [gitdagray](https://github.com/gitdagray)

**LinkedIn:**  [/in/davidagray](https://www.linkedin.com/in/davidagray/) 

**Patreon:**  [Join my Support Team!](patreon.com/davegray)

**Buy Me A Coffee:**  [You will have my sincere gratitude](https://www.buymeacoffee.com/davegray)   

Thank you for joining me on this journey. 

Dave