CODEWITHSUNDEEP
javascripttypescripttypeorm
Gardezi
Gardezi

Reputation: 2842

How to perform a like query TypeORM

Hello guys I'm trying to find all the results that have a in them. I have tried a couple of ways but the problem is nothing works. It just returns an empty array

var data = await getRepository(User)
  .createQueryBuilder("user")
  .where("user.firstName = %:name%", { name: firstName })
  .getMany();

and something like this

var data = await getRepository(User)
  .createQueryBuilder("user")
  .where("user.firstName like %:name%", { name: firstName })
  .getMany();

but nothing is working. All of these are returning me a empty array. Can somebody help me out thanks

Upvotes: 84

Views: 152194

Answers (7)

Salah ED
Salah ED

Reputation: 635

using repositories i put it in the where clause for exemple :

let res = await this.trackingRepository.findAndCount({
      where: [{ username : Like(`%${searchValue}%`) },
              { action : Like(`%${searchValue}%`) },
              { ip : Like(`%${searchValue}%`) }],
      order: {
        [sortField]: sortOrder === "descend" ? 'DESC' : 'ASC',
      },
      skip: (current - 1) * pageSize,
      take: pageSize,
    });

Upvotes: -1

SonickSeven
SonickSeven

Reputation: 584

"typeorm": "^0.3.5"

With TyeORM and Mysql works this

import {Like} from "typeorm";

const loadedPosts = await UserRepository.find({
    where: {
      name: Like("%Mar%")
    }
});

And you can use this:

import {Like} from "typeorm";

const filters={
  name: 'Ca',
  age: '3'
}

const columnFilters={}
for(const i in filters){
  columnFilters[i]= Like(`%${filters[i]}%`)
}

const loadedPosts = await UserRepository.find({
    where: columnFilters
});

Upvotes: 1

Herman Demsong
Herman Demsong

Reputation: 341

var data = await  getRepository(User)
                        .createQueryBuilder("user")
                        .where("user.firstName ILIKE %q", {q:`%${VALUE_HERE}%` })
                .getMany();

This is how I do it. Hope it helps

Upvotes: 1

Gvozden Miskovic
Gvozden Miskovic

Reputation: 415

It seems that all of the answers as of writing including the accepted answer by pleerock are vulnerable to SQL injection unless the user input has been sanitized beforehand.

 var data = await getRepository(User)
              .createQueryBuilder("user")
              .where("user.firstName like :name", { name:`%${firstName}%`})
              .getMany();

The fact that the above code is valid in TypeORM makes it so that any query of this style is vulnerable to data exfiltration. Imagining the following similar query:

 const data = await getRepository(User)
          .createQueryBuilder("user")
          .where("user.firstName like :name", { name: firstName })
          .getOne();

Un-sanitized data coming in from the user containing % character being sent into firstName in the above query (e.g. let firstName = '%John') would allow a user to exfiltrate potentially private data about other users.

Hence, where the use case allows one should ensure that any user input is sanitized and any special characters are removed.

Alternatively, in MySQL, where the use case demands that special characters are present in the text a full text search may be more appropriate. However, this is more expensive to maintain.

Create fulltext search on relevant column and perform query

    export class User {
        @PrimaryGeneratedColumn()
        id: number;

        @Index({fulltext: true})
        @Column()
        name: string;
    }

    const data = await this.repository
         .createQueryBuilder()
         .select()
         .where('MATCH(name) AGAINST (:name IN BOOLEAN MODE)', {name: name})
         .getOne()

Upvotes: 7

pleerock
pleerock

Reputation: 18846

Correct way is:

 var data = await getRepository(User)
                  .createQueryBuilder("user")
                  .where("user.firstName like :name", { name:`%${firstName}%` })
                  .getMany();

Upvotes: 160

Carlos Júlio
Carlos Júlio

Reputation: 525

You can also use the database function for concatenation. In postgres for instance:

 var data = await getRepository(User)
              .createQueryBuilder("user")
              .where("user.firstName like '%' || :name || '%'", {name: firstName })
              .getMany();

Upvotes: 9

Gabriel Lupu
Gabriel Lupu

Reputation: 1819

TypeORM provides out of the box Like function. Example from their docs:

import {Like} from "typeorm";

const loadedPosts = await connection.getRepository(Post).find({
    title: Like("%out #%")
});

in your case:

var data = await getRepository(User).find({
    name: Like(`%${firstName}%`)
});

Upvotes: 128

Related Questions