MySQL full text seach

[hugofialho]

Suggestion

Suport full text search in mysql using match against

Examples

To query data i thought this:

{"where": {"fts": { "match": ["column1", "column2"], "against":"filter" }}}

I studied the code and implement this inside de method 'SQLConnector.prototype._buildWhere'

if (key === 'fts') {
  console.log("Full text search!!!");
  const matchObj = where[key]["match"];
  const match = Array.isArray(matchObj) 
    ? matchObj.map(c => self.columnEscaped(model, c)).join(",")
    : self.columnEscaped(model, matchObj);
  stmt.merge(`MATCH(${match}) AGAINST('${where[key]["against"]}')`);
  whereStmts.push(stmt);
  continue;
}

Acceptance criteria

There is any suggestion to enhance the solution?
This idea is in the scope of this project? If yes, how can i do a pull request?

[bajtos]

Hi @hugofialho, thank you for the suggestion. Do you happen to know if other SQL databases like PostgreSQL, MSSQL and Oracle support the same MATCH(...) AGAINST (...) syntax? If not, then we need to find a way how to implement your proposal in loopback-connector-mysql only.

Also your implementation is prone to SQL injection attacks, we need to find a way how to provide match and against values via query parameters, not directly in the query command. I am not very familiar with MySQL, perhaps ?? is the placeholder to use in the query?

[bajtos]

how can i do a pull request?

See https://loopback.io/doc/en/contrib/code-contrib.html to learn about the contribution process in general and https://github.com/strongloop/loopback-connector-mysql#running-tests for instructions on how to test the MySQL connector. Inside this repository (loopback-connector), we are using typical Node.js workflow - npm i to install dependencies, npm t to run the tests. There should be no extra setup required.

[hugofialho]

Hello @bajtos, unfortunatly each database has a unique sintax for full text search. I taked a look in loopback-connector-mysql and not see any part obvious wich can implement this suggestion. Do you have any adivise how can i implament this inside loopback-connector-mysql?

[bajtos]

Take a look at this pull request: loopbackio/loopback-connector-postgresql#460 It added support for a new custom where operator contains. I think you should be able to follow the same approach and add a new custom where operator like fts to the MySQL connector.

[akshatdubeysf]

@bajtos I am trying to implement this, and will raise a PR soon.

[akshatdubeysf]

I have raised a PR for this - loopbackio/loopback-connector-mysql#454

[achrinza]

Closing as done.