Typeorm Subquery

user_704586 0
Viewed 1329

This MS SQL query returns the end date 189 business days from a given start date (3/1/21) by querying a calendar table that excludes weekends and holidays. Here's the basic SQL query:

select Top 1 TheDate as EndDate
FROM (select Top 189 d.TheDate from DateDimension d
LEFT JOIN USHolidayDimension h
ON d.TheDate = h.TheDate
where d.TheDate>='03/01/2021'
and IsWeekend=0 and H.TheDate is null
order by d.TheDate) as BusinessDays
order by TheDate DESC

This query correctly returns a date of 11/24/21, which is 189 business days from 3/1/21.

I've tried a couple of ways to write it in QueryBuilder, but keep getting an error Cannot build query because main alias is not set (call qb#from method)"

Here's my 1st query:

const endDate = await getConnection()
.createQueryBuilder()
.select("select Top 1 TheDate as EndDate " +
"FROM (select Top 189 d.TheDate from DateDimension d" +
"LEFT JOIN USHolidayDimension h " +
"ON d.TheDate = h.TheDate " +
"where d.TheDate>='03/01/2021'" +
"and IsWeekend=0 and H.TheDate is null "+
"order by d.TheDate) as BusinessDays " +
"order by TheDate DESC")
.getRawMany();

Here's my 2nd attempt - same error message:

const endDate = await connection .createQueryBuilder() .select("Top 1 TheDate", "Top1")
.addSelect(subQuery => {
return subQuery
.select("Top 189 d.TheDate")
.from("DateDimension", "d")
.leftJoinAndSelect("USHolidayDimension", "h", "d.TheDate = h.TheDate")
.where( "d.TheDate>='03/01/2021'")
.andWhere("IsWeekend=0 and H.TheDate is null")
}, "businessDays")
.getMany();

Probably just a dumb mistake, but I can't seem to figure it out.

typeorm subquery
1 Answers

To do query using raw SQL as in your first example, you need to use EntityManager.Query as described under EntityManager API in the TypeOrm documentation.

How to get the EntityManager is described under What is EntityManager.

Example:

    const connection = await createConnection();
    const entitymanager = connection.manager;
    let result = await entitymanager.query("select top 1 TheDate from DateDimension order by TheDate desc");
    let theDate = result[0].TheDate;
user_1586750
Related