Fulltext query in MySQL with TypeORM

[MontoyaAndres]

Issue type:

[x] question
[ ] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x] latest
[ ] @next
[ ] 0.x.x (or put your version here)

How can I do a fulltext query in MySQL?

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 with the word "Programmer" or "I am a programmer in javascript" 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.

[vlapo]

You should write find like this:

const result = await connection.manager.getRepository(User).find({
   where: [{
        lastname: Like("%programmer%")
   }, {
        name: Like("%programmer%")
   }, {
        personalDescription: Like("%programmer%")
   }]
});

This is just simple example with LIKE.

I do not have experience with mysql fulltext feature, but if you want to use it you have to define fulltext index with @Index({ fulltext: true }) and use query builder to write own custom query http://typeorm.io/#/select-query-builder/adding-where-expression and use special sql syntax.

[MontoyaAndres]

@vlapo thanks! But there's no support for fulltext queries in typeorm? That's sad :(

[vlapo]

I think there is only support for creating fulltext index. But using query builder you can write any query and use mysql specific fulltext seach syntax :-)

[MontoyaAndres]

@vlapo do you have some example about this sintax with the param {fulltext:true} of typeorm?

[vlapo]

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.

[MontoyaAndres]

@vlapo thank you so much for your help!!!! God bless you

[vlapo]

You are welcome. Please close issue if your question is answered. Also better place for questions is https://stackoverflow.com

[MontoyaAndres]

Thanks, I asked in stack overflow but no one answered :(

[vlapo]

There is also slack

[ericwooley]

I believe that that example would be an sql injection vulnerability. You can see how you can safely use variables in the example here: https://typeorm.io/#/select-query-builder/what-is-querybuilder

[joyce-adelle]

const result = await connection.manager.getRepository(User)
.createQueryBuilder()
.select()
.where("MATCH(name) AGAINST (:searchTerm IN BOOLEAN MODE)", {
searchTerm: searchTerm
})
.getMany();

this can be used instead to remove sql injection vulnerability

[vjfuenzalida]

how do you include the boolean mode operators if passing the parameter via object (to avoid SQL injections)?