Full text search with Bookshelf.js

I have a table with a MySQL full text index and wanted to search it with Bookshelf.js. bookshelf.png There is actually an open issue to add full text index support to Knex, the query building underlying Bookshelf, but it hasn’t been implemented yet.

Bookshelf has no inbuilt method for performing such a query but it actually ended up being rather simple.

Assuming you have a valid Bookshelf model configured already, for example:

var Customer = Bookshelf.model.extend({
    tableName: 'customers'
});

(lifted verbatim from the Bookshelf docs)

Let’s say the customer has a bio field on which you’d like to perform a full text search. You can do so with the following syntax:

var searchTerm = <code to build your search term>
var customer = new Customer()
    .query("whereRaw", "MATCH (bio) AGAINST(" + searchTerm + ")")
    .fetch(); 

And that’s it! Full text search on the bio field.

One caveat, since you’re piping the searchTerm into this query directly via string concatenation and basically sidestepping the, make sure to escape the string to prevent against SQL injection.

You should follow me on Twitter: @victorquinn

 
25
Kudos
 
25
Kudos

Now read this

Desired: Oculus Rift as a monitor replacement

Just read Wired article Oculus Is Awesome for Games, But It’s the Future of Movies I had an idea I haven’t been able to shake since I first used an Oculus: I want an Oculus Rift instead of monitors for programming. I want to arrive at... Continue →

Subscribe to Victor Quinn

Don’t worry; we hate spam with a passion.
You can unsubscribe with one click.

O0xjqXEd8t0K1UBVU51