Can't use search feature using relation properties.

[H4ad]

The error that typeorm throws is: syntax error at or near \":\".

This problem happens with versions of typeorm above ~0.2.25, I have two microservices, one with ~0.2.25 which are ok and one with ~0.2.36 which generate the error.

The Problem

Using the following object as the value for the s query parameter:

{
  'perk.name': 'Name of perk'
}

The query generated by lib is:

"SELECT DISTINCT \"distinctAlias\".\"MarketPerkEntity_id\" as \"ids_MarketPerkEntity_id\", \"distinctAlias\".\"MarketPerkEntity_createdAt\" FROM (SELECT \"MarketPerkEntity\".\"createdAt\" AS \"MarketPerkEntity_createdAt\", \"MarketPerkEntity\".\"updatedAt\" AS \"MarketPerkEntity_updatedAt\", \"MarketPerkEntity\".\"id\" AS \"MarketPerkEntity_id\", \"MarketPerkEntity\".\"isActive\" AS \"MarketPerkEntity_isActive\", \"MarketPerkEntity\".\"price\" AS \"MarketPerkEntity_price\", \"MarketPerkEntity\".\"perkId\" AS \"MarketPerkEntity_perkId\", \"MarketPerkEntity\".\"perkType\" AS \"MarketPerkEntity_perkType\", \"perk\".\"createdAt\" AS \"perk_createdAt\", \"perk\".\"updatedAt\" AS \"perk_updatedAt\", \"perk\".\"id\" AS \"perk_id\", \"perk\".\"isActive\" AS \"perk_isActive\", \"perk\".\"name\" AS \"perk_name\", \"perk\".\"imageUrl\" AS \"perk_imageUrl\", \"perk\".\"modifierType\" AS \"perk_modifierType\", \"perk\".\"modifierValue\" AS \"perk_modifierValue\" FROM \"markets_perks\" \"MarketPerkEntity\" LEFT JOIN \"perks\" \"perk\" ON \"perk\".\"id\"=\"MarketPerkEntity\".\"perkId\" WHERE \"perk\".\"name\" LIKE :andWhereperk.name17025429645493) \"distinctAlias\" ORDER BY \"distinctAlias\".\"MarketPerkEntity_createdAt\" DESC, \"MarketPerkEntity_id\" ASC LIMIT 15"

The problem lies in where filters: WHERE \"perk\".\"name\" LIKE :andWhereperk.name17025429645493, maybe the typeorm changes the behavior of how parameter names are parsed in versions above ~0.2.25 which cannot parse when using filters like perk.name.

Possible Solution

I read the library source code and found this method called builderSetWhere and I put the following line of code:

const time = process.hrtime();
+const safeFieldName = field.replace(/./g, '_');
+const index = `${safeFieldName}${time[0]}${time[1]}`;
-const index = `${field}${time[0]}${time[1]}`;
const args = [
  { field, operator: isNull(value) ? '$isnull' : operator, value },
  index,
  builder,
];
const fn = condition === '$and' ? this.setAndWhere : this.setOrWhere;
fn.apply(this, args);

The solution strip the . dot in the field name by changing it to _ and now the filter works.

For who having the problem

Until the issue is fixed, I create the following service to extend the TypeOrmCrudService instead:

import { TypeOrmCrudService } from '@nestjsx/crud-typeorm';
import { SConditionKey, ComparisonOperator } from '@nestjsx/crud-request';
import { isNull } from '@nestjsx/util';
import { SelectQueryBuilder } from 'typeorm';

export class TypeOrmCrudServiceWithSupportForJoins<T> extends TypeOrmCrudService<T> {
  protected builderSetWhere(
    builder: SelectQueryBuilder<T>,
    condition: SConditionKey,
    field: string,
    value: any,
    operator: ComparisonOperator = '$eq',
  ) {
    const time = process.hrtime();
    const safeFieldName = field.replace(/./g, '_');
    const index = `${safeFieldName}${time[0]}${time[1]}`;
    const args = [
      { field, operator: isNull(value) ? '$isnull' : operator, value },
      index,
      builder,
    ];
    const fn = condition === '$and' ? this.setAndWhere : this.setOrWhere;
    fn.apply(this, args);
  }
}

And instead it extends from TypeOrmCrudService, we will extends from TypeOrmCrudServiceWithSupportForJoins until the problem is fixed.

Edit: I found this issue #728, looks related and fixes the bug I'm reporting. Edit2: The same author of this PR is the author of other PR Typeorm#8081 that introduces the bug in this library.

[jodaltro]

I suggest to edit the archive on node_modules and use the patch-package. Work like a charm to me.

[H4ad]

@jodaltro It's not necessary, just change the extended class to the one I put in the description, your solution is only needed when you have some library that uses nestjsx/crud.