Drizzle TeamDT
Drizzle Team•2y ago
Fabian B.

How do I write a subquery inside select?

I have this SQL which is doing what I want

SELECT 
    "job_types"."id", 
    "job_types"."name",
    (
        SELECT 
            json_build_array("job_types_parent"."id", "job_types_parent"."name")
        FROM 
            "job_types" "job_types_parent"
        WHERE 
            "job_types_parent"."id" = "job_types"."parent_id"
        LIMIT 1
    ) AS "parent"
FROM 
    "job_types"
WHERE 
    "job_types"."parent_id" IS NOT NULL;


The query selection representation for this would be

const allJobTypesQuery = db.query.jobTypes.findMany({
  columns: {
    id: true,
    name: true,
  },
  with: {
    parent: {
      columns: {
        id: true,
        name: true,
      },
    },
  },
  where: (t, { isNotNull }) => isNotNull(t.parentId),
});


which also works. But I need a representation in a normal select query. I am trying to wrap my head around this, but it just does not seem to work. So far I got this, but not sure if I am on the right path here. I don't see how I can select from the subquery in allJobTypesManual.

const jobTypesParent = aliasedTable(jobTypes, "jobTypes_parent");

const sq = db
  .select({
    data: sql`json_build_array("parent"."id", "parent"."name")`.as('parent_data'),
  })
  .from(jobTypesParent)
  .where(eq(jobTypesParent.id, jobTypes.parentId))
  .limit(1)
  .as("parent");

const allJobTypesManual = await db
  .select({
    jobType: {
      id: jobTypes.id,
      name: jobTypes.name,
    },
    // how do I select the subquery fields here?
  })
  .from(jobTypes)
  .where(isNotNull(jobTypes.parentId))
  .groupBy(jobTypes.id);


Thanks in advance!
Was this page helpful?