CODEWITHSUNDEEP
javascriptmysqlnode.jstypeorm
Andrés Montoya
Andrés Montoya

Reputation: 4427

Search values from columns with MySQL and TypeORM

How can I do a fulltext query in MySQL with TypeORM in its latest version?

I want to search all the people that has a determinate word or phrase. For example, in the table "People" has:

@Column("varchar")
name: string;

@Column("varchar")
lastname: string;

@Column("text")
personalDescription: string;

So if I enter "Andrés", "Niko", "I am developer" or whatever, I want to find who have that info through the functionality "Full-text" of MySQL among the name, lastname, and personalDescription of an user.

Thanks.

Upvotes: 0

Views: 4958

Answers (2)

Carlos Vallejo
Carlos Vallejo

Reputation: 3980

I leave here this example to to it with JOINS

const awards = await connection.manager.getRepository(Awards)
            .createQueryBuilder("a")
            .innerJoinAndSelect('a.category', 'c')
            .innerJoinAndSelect('a.brand', 'b')
            .Where(
              `CONCAT(
                '', 
                a.name,   // Look for a coincidence on award name 
                a.model,  // Look for a coincidence in model award
                c.name,   // Look for a coincidence in category name
                b.name    // Look for a coincidence in brand name
              ) LIKE '%${toSearch}%'`,
            )
            .getMany();

You have to replace toSearch with the word to look for

Upvotes: 1

vlapo21
vlapo21

Reputation: 344

I do not have much experience with mysql fulltext. But my simple test is running.

Entity: 

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

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

    @Index({ fulltext: true })
    @Column("varchar")
    lastname: string;

    @Index({ fulltext: true })
    @Column("text")
    personalDescription: string;
}

Select using query builder:

const searchTerm = "programmer";

const result = await connection.manager.getRepository(User)
            .createQueryBuilder()
            .select()
            .where(`MATCH(lastname) AGAINST ('${searchTerm}' IN BOOLEAN MODE)`)
            .orWhere(`MATCH(name) AGAINST ('${searchTerm}' IN BOOLEAN MODE)`)
            .orWhere(`MATCH(personalDescription) AGAINST ('${searchTerm}' IN BOOLEAN MODE)`)
            .getMany();

If you need more advanced fulltext search you have to check mysql documentation.

Upvotes: 6

Related Questions