What is wrong with the parameters in my TypeORM WHERE clause for the QueryBuilder?

Question

Can someone explain to me what I am doing wrong when using the parameters for my where clause?

This next block gives me the error below it:

@EntityRepository(Something)
export class SomethingRepository extends Repository<Something>{

  findByUserAndSomethingById(userId: number, spotId: number){
    const thing = this.createQueryBuilder('something')
    .where('something.userId = :id', {id: userId})
    .andWhere('something.id = :id',{id: spotId}).getOne();
    return thing;
  }
}

QueryFailedError: column something.userid does not exist

This request gives me the right result.

@EntityRepository(Something)
export class SomethingRepository extends Repository<Something>{

  findByUserAndSomethingById(userId: number, spotId: number){
    const thing = this.createQueryBuilder('something')
    .where(`"something"."userId" = ${userId}`)
    .andWhere('something.id = :id',{id: spotId}).getOne();
    return thing;
  }
}

Update: Example repo for reproduction and typeorm issue on github.

Solution 1

The solution is to load the relations of my entity. As I understand it.

findByUserAndSomethingById(userId: number, spotId: number) {
    const thing = this.createQueryBuilder('something')
    .innerJoin('something.user', 'user')
      .where('user.id = :uid', { uid: userId })
      .andWhere('something.id = :sid', { sid: spotId }).getOne();
    return thing;
}

Thanks to @Mukyuu for all your effort to help me.

Solution 2

The issue with the original query is that the parameter name id was used more than once:

    .where('something.userId = :id', {id: userId})
    .andWhere('something.id = :id',{id: spotId}).getOne();

These need to be unique according to this note in the docs.

Solution 3

I can't say for sure since it wasn't in documentation. But when I use the TypeORM QueryBuilder to run a query to SQL, usually there is a need to add another quotation marks before and after the alias and field name.

For example in your case, you need to use: .where('"something"."userId"' = :id', {id: userId}) as how you would use in your second example: .where('"something"."userId"' = ${userId}).

One way to debug usually to check on the executed query which fails. Whether or not all query was executed as you would execute them normally or there are missing quotation marks.

Comments

[Mukyuu]

Are you able to read which query being executed in your project?

[fabianmoronzirfas]

Yes I can. Take a look at this gist gist.github.com/fabianmoronzirfas/…

[fabianmoronzirfas]

I think I know where this is coming from. Error message is 'column bathingspot.userid does not exist' the hint for that is 'Perhaps you meant to reference the column "bathingspot.userId" It seems like the camelcase in the string where the parameters are applied is beeing "uncamelcased" somehow. gist.githubusercontent.com/fabianmoronzirfas/…

[Mukyuu]

The only difference I could notice was the ' before and after the query between the failing query and successful query from gist.githubusercontent.com/fabianmoronzirfas/…. Also, probably $ is missing from 2 in working query? WHERE "bathingspot"."userId" = 2 AND "bathingspot"."id" = $1 from gist.github.com/fabianmoronzirfas/…

[fabianmoronzirfas]

Let us continue this discussion in chat.