Full-text search support

[techniq]

I'm proposing to add the search query operator to support full-text search

Comment.filter({
  where: {
    content: {
      'search': 'some search terms'
    }
  }
});

you could also specify multiple columns using a comma separated field (similar to how near queries do for lat/long columns)

Comment.filter({
  where: {
    'title,content': {
      'search': 'some search terms'
    }
  }
});

Currently knex has an open issue since 2014 to (generically) support full-text searches. In the interim, we can do as other have and use whereRaw and syntax based on the database type.

MySQL

I currently have been using MySQL (Google Cloud SQL) and plan to implement this first as it's pretty straight forward. Add a fulltext to the column(s) designed and then use match(column_name) against ('seach terms')

alter table `comment` add fulltext(`content`);
select * from comment where match(`content`) against ('some word')

-- or multiple columns
alter table `comment` add fulltext(`title`, `content`);
select * from comment where match(`title`, `content`) against ('some word')

Need to determine is we should always use boolean full-text searches

• https://blog.marceloaltmann.com/en-using-the-mysql-fulltext-index-search-pt-utilizando-mysql-fulltext/

Postgres

While I haven't looked into it much yet, Postgres appears a little more complicated and need to better understand tsvector. The following articles look like a good start.

• http://rachbelaid.com/postgres-full-text-search-is-good-enough/
• https://blog.lateral.io/2015/05/full-text-search-in-milliseconds-with-postgresql/
• http://shisaa.jp/postset/postgresql-full-text-search-part-1.html

[techniq]

Until it's decided how best to handle this, as of version 0.11.10, you can register a custom query operator and provide your own implementation (or override an existing one). For example, here's what I (roughly) use for MySQL

var adapter = new DSSqlAdapter({
  ...
  queryOperators: {
    'search': (query, field, value) => {
      return query.whereRaw(`MATCH(${field}) AGAINST(? IN BOOLEAN MODE)`, value)
    },
    '|search': (query, field, value) => {
      return query.orWhereRaw(`MATCH(${field}) AGAINST(? IN BOOLEAN MODE)`, value)
    }
  }
}