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)

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.

The strange part is that neither alias is referred to in the main query. Instead, I need to refer to the shipWeekQuery throughout.

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.