Search values from columns with MySQL and TypeORM

Question

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.

Answer 1

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.