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;
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),
});
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);
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!
1 Reply
Fabian B.
Fabian B.OP10mo ago
Nevermind, I think the most simplest solution works for this case.
const parent = aliasedTable(jobTypes, "parent");

const jobTypesData = await db
.select({
jobType: jobTypes,
parent: parent,
})
.from(jobTypes)
.innerJoin(parent, eq(jobTypes.parentId, parent.id));
.where(isNotNull(jobTypes.parentId))
const parent = aliasedTable(jobTypes, "parent");

const jobTypesData = await db
.select({
jobType: jobTypes,
parent: parent,
})
.from(jobTypes)
.innerJoin(parent, eq(jobTypes.parentId, parent.id));
.where(isNotNull(jobTypes.parentId))
Want results from more Discord servers?
Add your server