VirtualColumn Decorator

[CollinCashio]

Feature Description

I would like to add a new feature to TypeORM so that I can consolidate my code and simply run a subSelect with every get request on the requested entity. This would improve performance and allow me to utilize the full potential of the supported relational databases PostgresQL, MySql, and MSSQL.

Below is what I am planning on implementing and tying to this Issue once it is completed (I will be coding this up tonight and think that it would bring value to this project overall)

The Problem

When querying data against any relational database there is often a need to return some query (a sub-select, count, etc.) that returns with a model. For example, if we create an entity of type Company that has a one-to-many relationship on employees, it may make sense to have a totalEmployeesCount column ("Virtual Column") that calculates this on every request or even better when the column is selected in the base entity query. This is what I am wanting to implement in TypeORM. This is common in most ORM solutions and I would love to see it here.

The Solution

Let's use the same example above: we have entity of type Company that has a one-to-many relationship on an employee entity (employees), it may make sense to have a totalEmployeesCount column that could be re-calculated on every fetch of the entity:

import { BaseEntity, PrimaryColumn, OneToMany, ManyToOne, VirtualColumn } from "typeorm";

@Entity({ name: "companies" })
export class Company extends BaseEntity {
  @PrimaryColumn("varchar", { length: 50 })
  name: string;

  @VirtualColumn({ query: (alias) => `SELECT COUNT("name") FROM "employees" WHERE "companyName" = ${alias}.name` })
  totalEmployeesCount: number;

  @OneToMany((type) => Employee, (employee) => employee.company)
  employees: Employee[];
}

@Entity({ name: "employees" })
export class Employee extends BaseEntity {
  @PrimaryColumn("varchar", { length: 50 })
  name: string;

  @ManyToOne((type) => Company, (company) => company.employees)
  company: Company;
}

Some notes on the VirtualColumn Decorator:

1. The "query" property will be used to populate the column data. This query is used when generating the relational db script. The query function is called with the current entities alias automatically generated by TypeORM.

2. A virtual column is not an editable column. It is a record that should be considered as readonly and it is ignored when the save entity function is run.

Considered Alternatives

One workaround would be to create a query builder but in doing so, this would add complexity, and it doesn't solve the overall need: code redundancy, and consistent entity result sets.

The second workaround would to be to add an @AfterLoad() decorator and perform a query there, but this is a poor solution since it would not allow us to utilize the relational databases query optimization engine when performing a query leading to worse performance. This proposed solution would be faster and avoid several unnecessary database hits.

Relevant Database Driver(s)

This solution would work for all relational databases (it is possible to add non-relational databases to this as well but the immediate need is for relational databases.

DB Type	Relevant
aurora-mysql	yes
aurora-postgres	yes
better-sqlite3	yes
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	yes
nativescript	no
oracle	no
postgres	yes
react-native	no
sap	no
spanner	no
sqlite	yes
sqlite-abstract	yes
sqljs	yes
sqlserver	yes

Are you willing to resolve this issue by submitting a Pull Request?

• ✅ Yes, I have the time, and I know how to start. (Already working on a solution)
• ✖️ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[mag-nikita-ks]

does someone check performance between VirtualColumn and simple querybuilder? i have an issue with it, i have checked and found out that using VirtualColumn is slower x3 than querybuilder with same data
here is example of query
const query = this.repository .createQueryBuilder('project') .leftJoin( (subQuery) => subQuery .select('"projectId"') .addSelect('SUM(CASE WHEN billable = true THEN duration ELSE 0 END)', 'timeBillableDuration') .addSelect('SUM(CASE WHEN billable = false THEN duration ELSE 0 END)', 'timeDuration') .from(TimeEntry, 'TE') .groupBy('"projectId"'), 'time', '"project"."id" = "time"."projectId"', ) .addSelect('"time"."timeBillableDuration"', 'timeBillableDuration') .addSelect('"time"."timeDuration"', 'timeDuration')

virtual column

querybuilder

[dipeshba]

Can some more documentation be added on how this works? Can we pass current record values in query that is defined by virtual column? Can we use only primary column in the query or other values can be passed as well?

[bartolomej]

Can some more documentation be added on how this works? Can we pass current record values in query that is defined by virtual column? Can we use only primary column in the query or other values can be passed as well?

I think you can find the docs here: https://orkhan.gitbook.io/typeorm/docs/decorator-reference#virtualcolumn