CODEWITHSUNDEEP
node.jstypescriptpostgresqltypeorm
Jaimin Dave
Jaimin Dave

Reputation: 1222

Adding OR condition with other than entity column in typeorm gives error: could not determine data type of parameter $2

I want to generate postgres query as below:

select * from table1 t1 inner join table2 t2 on t1.id = t2.table1_id
where param.name is null or t1.name ilike param.name

Here param is request parameter

I tried with this:

await this.userRepository.createQueryBuilder("t1")
        .innerJoinAndSelect("t1.table2","t2")
        .where("t1.name ilike :name or :name is null",{ name:`%${request.body.name}%`)
        .getMany()

This returns following error:

QueryFailedError: could not determine data type of parameter $2

Upvotes: 0

Views: 890

Answers (2)

Jaimin Dave
Jaimin Dave

Reputation: 1222

I used below query to meet my need:

    await this.userRepository.createQueryBuilder("t1")
               .andWhere(new Brackets(q => {
                    q.where("d.name ilike :name", { name:`%${request.body.name}%`})                      
                    q.orWhere("cast(:param as character varying) is null", { param: request.body.name || null})
                }))

Upvotes: 0

Lakhdar
Lakhdar

Reputation: 422

I just tried the example and got the same error, from my experience I think this is a bug with typeorm

I suggest you change your aproach to the problem, try this:

await this.userRepository.createQueryBuilder("t1")
        .innerJoinAndSelect("t1.table2","t2")
        .where("t1.name ilike :name",{ name: req.body.name ? `%${request.body.name}%` : '%%')
        .getMany()

this will work the same as the provided query.

Upvotes: 1

Related Questions