Support TypeORM VirtualColumn

[tripss]

Support TypeORM VirtualColumn

[TriPSs]

Just patched via patch-package, this seems to work great! Is there a reason why this isn't already supported?

Partly because what you are saying above, this solution is not correctly tested at all so can have unexpected behaviour with other drivers, also there was no response on the ticket for a while 😅

[YeomansIII]

Thanks @TriPSs !

[TriPSs]

Interesting, could it be because TypORM version is not the same? Do you use yarn by any change? If so could you try to add the following:

  "resolutions": {
    "typeorm": "0.3.11"
   }

Then do yarn again to reinstall deps and see if it works.

This is actually a quite awesome feature!

[YeomansIII]

Agreed, it's an awesome feature and also, I appreciate you taking over as maintainer of this project.

I use npm, but did something similar to pin the typeorm version

"overrides": {
    "@ptc-org/nestjs-query-graphql": {
      "typeorm": "0.3.11"
    }
}

But no luck, still getting the same error on filter and sort.

I edited the main post with this, just FYI:

I am able to successfully query the Field of the VirtualColumn, but as soon as I try to filter or sort, it says the column doesn't exist (it really doesn't, but the repository should handle that).

So selecting the VirtualColumn field does return the appropriate calculated value, it just doesn't work on filter and sort.

[TriPSs]

Okay thanks for the explanation, will try to check this out somewhere in the coming days.

[TriPSs]

After some digging it's because the query services tries to filter / order on it as a normal database field, like tabel.field but since it's not a existing field it does not work. When querying it it does work since TypeORM selects all fields as table.field as table_field.

We could make a simple fix by checking if the column we are filtering/ordering on has the property isVirtualProperty and if true add the order/filter with the _ instead of a ..

In the case of sorting it would look like:

  public applySorting<T extends Sortable<Entity>>(qb: T, sorts?: SortField<Entity>[], alias?: string): T {
    if (!sorts) {
      return qb
    }

    return sorts.reduce((prevQb, { field, direction, nulls }) => {
      const columnMetadata = this.repo.metadata.columns.find((col) => col.propertyName === field)

      let col = alias ? `${alias}.${field as string}` : `${field as string}`
      // If the column is virtual we need to use the actual selected field as it does not exist in the database
      if (columnMetadata?.isVirtualProperty) {
        col = `"${alias ? `${alias}_${field as string}` : `${field as string}`}"`
      }

      return prevQb.addOrderBy(col, direction, nulls)
    }, qb)
  }

[jbeck018]

Any update on this? Just bumped versions in Typeorm and would love to take advantage of nestjs-query functionality on the VirtualColumns.

[MrSquaare]

After some digging it's because the query services tries to filter / order on it as a normal database field, like tabel.field but since it's not a existing field it does not work. When querying it it does work since TypeORM selects all fields as table.field as table_field.

We could make a simple fix by checking if the column we are filtering/ordering on has the property isVirtualProperty and if true add the order/filter with the _ instead of a ..

In the case of sorting it would look like:

  public applySorting<T extends Sortable<Entity>>(qb: T, sorts?: SortField<Entity>[], alias?: string): T {
    if (!sorts) {
      return qb
    }

    return sorts.reduce((prevQb, { field, direction, nulls }) => {
      const columnMetadata = this.repo.metadata.columns.find((col) => col.propertyName === field)

      let col = alias ? `${alias}.${field as string}` : `${field as string}`
      // If the column is virtual we need to use the actual selected field as it does not exist in the database
      if (columnMetadata?.isVirtualProperty) {
        col = `"${alias ? `${alias}_${field as string}` : `${field as string}`}"`
      }

      return prevQb.addOrderBy(col, direction, nulls)
    }, qb)
  }

Just patched via patch-package, this seems to work great! Is there a reason why this isn't already supported?

[YeomansIII]

@MrSquaare, would you be able to make a PR?

[MrSquaare]

@MrSquaare, would you be able to make a PR?

@YeomansIII Unfortunately, after further testing, it doesn't seem to work with some databases (such as Postgres).
Also, I couldn't get it to work with the WHERE clause. Did you see somewhere that VirtualColumn should support the WHERE clause? (I've seen a few people mention that HAVING works with aliases, but I haven't tested it yet).
It seems that the only reliable way to get VirtualColumn to work with ORDER BY, WHERE, ... is via a sub query, which can have performance disadvantages. This remains a viable option.

[DevBrasil]

Any news about thaat issue ?

Having the same issue when using where contidional, that code fix the sort.

ERROR [ExceptionsHandler] Unknown column 'Quote.daysUntilDue' in 'where clause'
QueryFailedError: Unknown column 'Quote.daysUntilDue' in 'where clause'