typeorm: Cannot find alias for relation at ...

Issue Description

Using BaseEntity.find method and trying to filter by a property inside a related entity it throws the following error (this doesn’t happen if the property is the PK of the related entity):

Error: Cannot find alias for relation at currency
    at _loop_3 (C:\...\node_modules\.pnpm\typeorm@0.2.38_ioredis@4.28.0+pg@8.7.1\src\query-builder\QueryBuilder.ts:954:27)
    at SelectQueryBuilder.QueryBuilder.findColumnsForPropertyPath (C:\...\node_modules\.pnpm\typeorm@0.2.38_ioredis@4.28.0+pg@8.7.1\node_modules\typeorm\query-builder\QueryBuilder.js:827:27)
    at SelectQueryBuilder.<anonymous> (C:\...\node_modules\.pnpm\typeorm@0.2.38_ioredis@4.28.0+pg@8.7.1\src\query-builder\QueryBuilder.ts:1069:68)
    at step (C:\...\node_modules\.pnpm\tslib@2.3.1\node_modules\tslib\tslib.js:143:27)
    at Object.next (C:\...\node_modules\.pnpm\tslib@2.3.1\node_modules\tslib\tslib.js:124:57)
    at SelectQueryBuilder.QueryBuilder.getWhereCondition (C:\...\node_modules\.pnpm\typeorm@0.2.38_ioredis@4.28.0+pg@8.7.1\src\query-builder\QueryBuilder.ts:1208:80)
    at SelectQueryBuilder.where (C:\...\node_modules\.pnpm\typeorm@0.2.38_ioredis@4.28.0+pg@8.7.1\src\query-builder\SelectQueryBuilder.ts:721:32)
    at Function.FindOptionsUtils.applyOptionsToQueryBuilder (C:\...\node_modules\.pnpm\typeorm@0.2.38_ioredis@4.28.0+pg@8.7.1\src\find-options\FindOptionsUtils.ts:184:16)
    at Function.FindOptionsUtils.applyFindManyOptionsOrConditionsToQueryBuilder (C:\...\node_modules\.pnpm\typeorm@0.2.38_ioredis@4.28.0+pg@8.7.1\src\find-options\FindOptionsUtils.ts:72:25)
    at EntityManager.<anonymous> (C:\...\node_modules\.pnpm\typeorm@0.2.38_ioredis@4.28.0+pg@8.7.1\src\entity-manager\EntityManager.ts:677:26)

Expected Behavior

I expect it’s possible to find rows filtering by the value of any property of a related entity and not only by its PK.

Actual Behavior

It throws Error: Cannot find alias for relation at .... See above for the complete stacktrace.

Steps to Reproduce

Example:

Entity

@Entity('countries')
export class CountryEntity extends BaseEntity {
    @PrimaryColumn({length: 2})
    id: string;

    ...

    @ManyToOne(() => CurrencyEntity, { nullable: true, eager: true })
    currency: CurrencyEntity;
}
@Entity('currencies')
export class CurrencyEntity extends BaseEntity {
    @PrimaryColumn()
    id: string;

    ...

    @Column()
    fullName: string;
}

Lets say we want to find every single country which official currency’s fullName is United States dollar

const markets = await CountryEntity.find({
    where: {
        currency:  {
            fullName: 'United States dollar'
        }
    }
});

Result: Error: Cannot find alias for relation at currency.

If you search by PK currency.id instead of fullName, it would work:

const markets = await CountryEntity.find({
    where: {
        currency:  {
            id: 'USD'
        }
    }
});

Using QueryBuilder also works.

My Environment

Dependency Version
Operating System Windows 10
Node.js version 16.3.0
Typescript version 4.4.4
TypeORM version 0.2.38

Additional Context

Relevant Database Driver(s)

DB Type Reproducible
aurora-data-api no
aurora-data-api-pg no
better-sqlite3 no
cockroachdb no
cordova no
expo no
mongodb no
mysql no
nativescript no
oracle no
postgres yes
react-native no
sap no
sqlite no
sqlite-abstract no
sqljs no
sqlserver no

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

  • ✅ Yes, I have the time, but I don’t know how to start. I would need guidance.

About this issue

  • Original URL
  • State: open
  • Created 5 years ago
  • Reactions: 14
  • Comments: 18

Most upvoted comments

I got the same error, I fixed by specifying relations in the condition

Try with:

const markets = await CountryEntity.find({
    relations: ['currency'],
    where: {
        currency:  {
            fullName: 'United States dollar'
        }
    }
});

Note: the downside is that the currency entity with by pulled also, it means, country’s currency will be populated.

@TracyLDavidson I’m having this issue with the delete method right now, I was trying to delete a related entity using a non-primary column. My workaround was passing id: Not(IsNull())

It would be something like this:

await countryEntitiesRepository.delete({
    currency: {
        id: Not(IsNull()),
        fullName: 'United States dollar'
    }
})

Can we open PR for this @lucas-labs. I can submit the fix if needed

Do we have any updates on this issue? because it would be nice not to be forced to populate extra objects if not needed

@Vtchoo that remove the error but the condition on the non-primary column is totally ignored in my case…

I can confirm that. We had similar code like

await appointment.delete({
    date: MoreThanOrEqual(dateInput),
    period: { // this is a relation of appointment
        id: Not(IsNull()),
        paricipantId // this is a foreign key
    }
})

This results in the following SQL

DELETE FROM "appointment" WHERE ("date" >= $1 AND NOT("periodId" IS NULL))

So you see, the additional part about the very important participantId is missing completely.

Also first adding the relation, like you do e.g. in find (see code below) does not work, as delete doesn’t offer the opportunity as far as I know (only FindOptionsWhere<T> is possible, but not FindManyOptions<T>)

const appointments = await appointmentRepository.find({
        where: {
          date: MoreThanOrEqual(dateInput),
          period: {
            participantId,
          },
        },
        relations: {
          period: true,
        },
      })

Our workaround here was to first fetch all periods, and then map over the Ids. Not the most efficient way, but readable and bullet proof. Or you just find the appointments and then delete them, might be even better.

const periods = await periodRepository.find({
      where: {
        participantId,
        to: MoreThanOrEqual(dateInput),
      },
})
// Delete all appointments
await appointmentRepository.delete({
      date: MoreThanOrEqual(dateInput),
      period: {
        id: In(periods.map(p => p.id)),
      },
})

// OR

// Find all appointments after the dateInput
const appointmentsToDelete =
  await appointmentRepository.find({
    where: {
      date: MoreThanOrEqual(dateInput),
      period: {
        participantId,
      },
    },
    relations: {
      period: true,
    },
  })

// Delete all appointments
await appointmentRepository.delete(
  appointmentsToDelete.map(va => va.id)
)

It took us sooo long to find this minor difference, as the IDE also does not show anything, that this might not be possible… I’d consider this inconsistency a bug and would be happy if delete could also handle FindManyOptions<T>.

I’m having this error with delete method. It works normally for finding, but I notice that when generating the query, it generates a random string as an alias for the related table:

SELECT CountryEntity.id from country_entity CountryEntity  LEFT JOIN currency "cef765fb0a58526dc35bebe4f53" ON "cef765fb0a58526dc35bebe4f53".id = CountryEntity.currency_id;

This used to work, seems to got broken after 0.2.34

Can confirm that the ‘cannot find alias’ issue was not a problem before.

UPDATE: I was having the ‘cannot find alias’ error on the save method. The reason was that the ID I was providing in a joined relationship was null which caused the error.

Example:

await this.carsRepository.save({
  ...car,
  user: {
    id: car.user.id,   // -> This ID was null, which then caused the error 'cannot find alias for relation user'
    firstName: car.user.firstName,
  },
});

Soo, be careful with those null/undefined IDs inside relations on save method!

@Vtchoo that remove the error but the condition on the non-primary column is totally ignored in my case…

maybe work

const markets = await CountryEntity.find({
    where: {
        currency:  {
            fullName: 'United States dollar'
        }
    },
    relations: ['currency'],
});

any updates?