Fulltext search support

[czkody]

Hello Diogo,
is there any way to use fulltext search, especially MATCH ... AGAINST in mysql ? I think it should be solved by adding fulltext search operator in condition and sort definitions, or by adding query customization support or by stored procedure encapsulation support.

Thank you very much for your work on the project.

Kody

[dresende]

I would like to have this added but I'm not sure if this is used more often in conditions or directly as a select column.

SELECT MATCH('col1', 'col2') AGAINST ('expression') AS score FROM table ORDER BY score DESC

This is what you want right? Or you have a use case to use it in WHERE ?

[czkody]

I think more often is use in WHERE and ORDER BY. There should be model attributes in select list.

SELECT 
    `attr1`, `attr2`, `attr3` 
FROM 
    `table` 
WHERE 
    MATCH(`attr1`, `attr2`) AGAINST ('expression') 
ORDER BY 
    MATCH(`attr1`, `attr2`) AGAINST ('expression') DESC

[dresende]

Something like this?

Model.find({ score: db.match("attr1", "attr2").against("expression") }).order("-score").get(function (err, items) {
    // ...
});

[dxg]

Hmm MATCH AGAINST is non standard SQL which only works with MySQL.
Would it not be wiser to keep ORM database server agnostic and just have Model.find_by_sql('SELECT ... MATCH ... AGAINST') for custom queries?
Or even partial sql queries: Model.find({ ... }).where_sql('MATCH ... AGAINST ...').run(...)

[dresende]

I think pg also supports it. I still have to think about it because sqlite probably doesn't. If it doesn't we can follow your suggestion of where_sql.

[dxg]

Postgres supports full text search, but the syntax is completely different.
MATCH and AGAINST are not reserved words. Frustrating..

[czkody]

I think the syntax should be solved at the driver level and ORM should only have information about full-text search support of the driver.

[dresende]

Yes, it's probably better to have a common syntax. For drivers that do not support it at all I think we could just throw a message. Maybe we could make some kind of global setting similar to use strict (default true). If you want to have access to specific functions of some drivers (even with a common syntax) you had to disable strict mode.

This way people using the library to have a cross database support are ok and people just wanting an abstraction layer to a specific database can disable strict mode and use more functionalities.

[dxg]

Another option is having plugins/modules for ORM. Eg:

var orm = require('orm');
var mysqlTextSearch = require('orm-mysql-text-search');

orm.use(mysqlTextSearch);

orm.connect(...)

I suggest this because I struggle to see how the mysql & postgres drivers could have a common full text search syntax. I reviewed the doco for each; The features & functionality are vastly different:

Mysql:

SELECT *
FROM articles
WHERE MATCH (title,body)
AGAINST ('oranges' IN NATURAL LANGUAGE MODE);

Postgres:

SELECT id, ts_headline(body, q), rank
FROM (SELECT id, body, q, ts_rank_cd(ti, q) AS rank
      FROM apod, to_tsquery('stars') q
      WHERE ti @@ q
      ORDER BY rank DESC
      LIMIT 10) AS foo;

Plugins would probably require a lot more work though..

[dresende]

Yes, I like the plugins idea (I'm a fan of plugins!). I'm going to make a small .use() on db (it's probably better to use the plugin after connection so the plugin can check if it's compatible) and I'm going to make an initial mysql fts plugin. @dxg you could then try and do the same for postgres :)

[dxg]

Sounds good!

[dresende]

I'm still working on this but I hope I'll have some good news this week :)

[dresende]

Take a look at this plugin https://github.com/dresende/node-orm-mysql-fts and give me some feedback :)

[dxg]

Looks great! I like how simple the code is.