<commit_before># AttrSearchable

[![Build Status](https://secure.travis-ci.org/mrkamel/attr_searchable.png?branch=master)](http://travis-ci.org/mrkamel/attr_searchable)
[![Code Climate](https://codeclimate.com/github/mrkamel/attr_searchable.png)](https://codeclimate.com/github/mrkamel/attr_searchable)
[![Dependency Status](https://gemnasium.com/mrkamel/attr_searchable.png?travis)](https://gemnasium.com/mrkamel/attr_searchable)
[![Gem Version](https://badge.fury.io/rb/attr_searchable.svg)](http://badge.fury.io/rb/attr_searchable)

![attr_searchable](https://raw.githubusercontent.com/mrkamel/attr_searchable_logo/master/attr_searchable.png)

AttrSearchable extends your ActiveRecord models to support fulltext search
engine like queries via simple query strings and hash-based queries. Assume you
have a `Book` model having various attributes like `title`, `author`, `stock`,
`price`, `available`. Using AttrSearchable you can perform:

```ruby
Book.search("<NAME> <NAME>")
Book.search("author: Rowling title:'<NAME>'")
Book.search("price > 10 AND price < 20 -stock:0 (Potter OR Rowling)")
# ...
```

Thus, you can hand out a search query string to your models and you, your app's
admins and/or users will get powerful query features without the need for
integrating additional third party search servers, since AttrSearchable can use
fulltext index capabilities of your RDBMS in a database agnostic way (currently
MySQL and PostgreSQL fulltext indices are supported) and optimizes the queries
to make optimal use of them. Read more below.

Complex hash-based queries are supported as well:

```ruby
Book.search(:author => "Rowling", :title => "<NAME>")
Book.search(:or => [{:author => "Rowling"}, {:author => "Tolkien"}])
Book.search(:and => [{:price => {:gt => 10}}, {:not => {:stock => 0}}, :or => [{:title => "Potter"}, {:author => "Rowling"}]])
Book.search(:or => [{:query => "Rowling -Potter"}, {:query => "Tolkien -Rings"}])
# ...
```

## Installation

For Rails/ActiveRecord 3 (or 4), add this line to your application's Gemfile:

    gem 'attr_searchable'

And then execute:

    $ bundle

Or install it yourself as:

    $ gem install attr_searchable

## Usage

To enable AttrSearchable for a model, `include AttrSearchable` and specify the
attributes you want to expose to search queries:

```ruby
class Book < ActiveRecord::Base
  include AttrSearchable

  attr_searchable :title, :description, :stock, :price, :created_at, :available
  attr_searchable :comment => ["comments.title", "comments.message"]
  attr_searchable :author => "author.name"
  # ...

  has_many :comments
  belongs_to :author
end
```

## How does it work

AttrSearchable parses the query and maps it to an SQL Query using Arel.
Thus, AttrSearchable is not bound to a specific RDBMS.

```ruby
Book.search("stock > 0")
# ... WHERE books.stock > 0

Book.search("price > 10 stock > 0")
# ... WHERE books.price > 10 AND books.stock > 0

Book.search("Harry Potter")
# ... WHERE (books.title LIKE '%Harry%' OR books.description LIKE '%Harry%' OR ...) AND (books.title LIKE '%Potter%' OR books.description LIKE '%Potter%' ...)

Book.search("available:yes OR created_at:2014")
# ... WHERE books.available = 1 OR (books.created_at >= '2014-01-01 00:00:00' and books.created_at <= '2014-12-31 00:00:00')
```

Of course, these `LIKE '%...%'` queries won't achieve optimal performance, but
check out the section below on AttrSearchable's fulltext capabilities to
understand how the resulting queries can be optimized.

As `Book.search(...)` returns an `ActiveRecord::Relation`, you are free to pre-
or post-process the search results in every possible way:

```ruby
Book.where(:available => true).search("<NAME>").order("books.id desc").paginate(:page => params[:page])
```

## Fulltext index capabilities

By default, i.e. if you don't tell AttrSearchable about your fulltext indices,
AttrSearchable will use `LIKE '%...%'` queries. Unfortunately, unless you
create a [trigram index](http://www.postgresql.org/docs/9.1/static/pgtrgm.html)
(postgres only), theses queries can not use SQL indices, such that every row
needs to be scanned by your RDBMS when you search for `Book.search("<NAME>")` or similar. Therefore, AttrSearchable can exploit the fulltext index
capabilities of MySQL and PostgreSQL. To use already existing fulltext indices,
simply tell AttrSearchable to use them via:

```ruby
class Book < ActiveRecord::Base
  # ...

  attr_searchable_options :title, :type => :fulltext
  attr_searchable_options :author, :type => :fulltext

  # ...
end
```

AttrSearchable will then transparently change its SQL queries for the
attributes having fulltext indices to:

```ruby
Book.search("<NAME>")
# MySQL: ... WHERE (MATCH(books.title) AGAINST('+Harry' IN BOOLEAN MODE) OR MATCH(books.author) AGAINST('+Harry' IN BOOLEAN MODE)) AND (MATCH(books.title) AGAINST ('+Potter' IN BOOLEAN MODE) OR MATCH(books.author) AGAINST('+Potter' IN BOOLEAN MODE))
# PostgreSQL: ... WHERE (to_tsvector('simple', books.title) @@ to_tsquery('simple', 'Harry') OR to_tsvector('simple', books.author) @@ to_tsquery('simple', 'Harry')) AND (to_tsvector('simple', books.title) @@ to_tsquery('simple', 'Potter') OR to_tsvector('simple', books.author) @@ to_tsquery('simple', 'Potter'))
```

Obviously, theses queries won't always return the same results as wildcard
`LIKE` queries, because we search for words instead of sub-strings. However,
fulltext indices will usually of course provide better performance.

Moreover, the query above is not yet perfect. To improve it even more,
AttrSearchable tries to optimize the queries to make optimal use of fulltext
indices while still allowing to mix them with non-fulltext attributes. To
improve queries, the first thing you want to do is to specify a default field
to search in, such that AttrSearchable must no longer search within all fields:

```ruby
attr_searchable :all => [:author, :title]
attr_searchable_options :all, :type => :fulltext, :default => true
```

Now AttrSearchable can optimize the following, not yet optimal query:

```ruby
BookSearch("Rowling OR Tolkien stock > 1")
# MySQL: ... WHERE ((MATCH(books.author) AGAINST('+Rowling' IN BOOLEAN MODE) OR MATCH(books.title) AGAINST('+Rowling' IN BOOLEAN MODE)) OR (MATCH(books.author) AGAINST('+Tolkien' IN BOOLEAN MODE) OR MATCH(books.title) AGAINST('+Tolkien' IN BOOLEAN MODE))) AND books.stock > 1
# PostgreSQL: ... WHERE ((to_tsvector('simple', books.author) @@ to_tsquery('simple', 'Rowling') OR to_tsvector('simple', books.title) @@ to_tsquery('simple', 'Rowling')) OR (to_tsvector('simple', books.author) @@ to_tsquery('simple', 'Tolkien') OR to_tsvector('simple', books.title) @@ to_tsquery('simple', 'Tolkien'))) AND books.stock > 1
```

to the following, more performant query:

```ruby
BookSearch("Rowling OR Tolkien stock > 1")
# MySQL: ... WHERE MATCH(books.author, books.title) AGAINST('Rowling Tolkien' IN BOOLEAN MODE) AND books.stock > 1
# PostgreSQL: ... WHERE to_tsvector('simple', books.author || ' ' || books.title) @@ to_tsquery('simple', 'Rowling | Tokien') and books.stock > 1
```

Other queries will be optimized in a similar way, such that AttrSearchable
tries to minimize the fultext constraints within a query, namely `MATCH()
AGAINST()` for MySQL and `to_tsvector() @@ to_tsquery()` for PostgreSQL.

```ruby
BookSearch("(Rowling -Potter) OR Tolkien")
# MySQL: ... WHERE MATCH(books.author, books.title) AGAINST('(+Rowling -Potter) Tolkien' IN BOOLEAN MODE)<commit_msg>Readme updates [skip-ci]<commit_after># AttrSearchable

[![Build Status](https://secure.travis-ci.org/mrkamel/attr_searchable.png?branch=master)](http://travis-ci.org/mrkamel/attr_searchable)
[![Code Climate](https://codeclimate.com/github/mrkamel/attr_searchable.png)](https://codeclimate.com/github/mrkamel/attr_searchable)
[![Dependency Status](https://gemnasium.com/mrkamel/attr_searchable.png?travis)](https://gemnasium.com/mrkamel/attr_searchable)
[![Gem Version](https://badge.fury.io/rb/attr_searchable.svg)](http://badge.fury.io/rb/attr_searchable)

![attr_searchable](https://raw.githubusercontent.com/mrkamel/attr_searchable_logo/master/attr_searchable.png)

AttrSearchable extends your ActiveRecord models to support fulltext search
engine like queries via simple query strings and hash-based queries. Assume you
have a `Book` model having various attributes like `title`, `author`, `stock`,
`price`, `available`. Using AttrSearchable you can perform:

```ruby
Book.search("<NAME> <NAME>")
Book.search("author: Rowling title:'<NAME>'")
Book.search("price > 10 AND price < 20 -stock:0 (Potter OR Rowling)")
# ...
```

Thus, you can hand out a search query string to your models and you, your app's
admins and/or users will get powerful query features without the need for
integrating additional third party search servers, since AttrSearchable can use
fulltext index capabilities of your RDBMS in a database agnostic way (currently
MySQL and PostgreSQL fulltext indices are supported) and optimizes the queries
to make optimal use of them. Read more below.

Complex hash-based queries are supported as well:

```ruby
Book.search(:author => "Rowling", :title => "<NAME>")
Book.search(:or => [{:author => "Rowling"}, {:author => "Tolkien"}])
Book.search(:and => [{:price => {:gt => 10}}, {:not => {:stock => 0}}, :or => [{:title => "Potter"}, {:author => "Rowling"}]])
Book.search(:or => [{:query => "Rowling -Potter"}, {:query => "Tolkien -Rings"}])
# ...
```

## Installation

For Rails/ActiveRecord 3 (or 4), add this line to your application's Gemfile:

    gem 'attr_searchable'

And then execute:

    $ bundle

Or install it yourself as:

    $ gem install attr_searchable

## Usage

To enable AttrSearchable for a model, `include AttrSearchable` and specify the
attributes you want to expose to search queries:

```ruby
class Book < ActiveRecord::Base
  include AttrSearchable

  attr_searchable :title, :description, :stock, :price, :created_at, :available
  attr_searchable :comment => ["comments.title", "comments.message"]
  attr_searchable :author => "author.name"
  # ...

  has_many :comments
  belongs_to :author
end
```

## How does it work

AttrSearchable parses the query and maps it to an SQL Query using Arel.
Thus, AttrSearchable is not bound to a specific RDBMS.

```ruby
Book.search("stock > 0")
# ... WHERE books.stock > 0

Book.search("price > 10 stock > 0")
# ... WHERE books.price > 10 AND books.stock > 0

Book.search("<NAME>")
# ... WHERE (books.title LIKE '%Harry%' OR books.description LIKE '%Harry%' OR ...) AND (books.title LIKE '%Potter%' OR books.description LIKE '%Potter%' ...)

Book.search("available:yes OR created_at:2014")
# ... WHERE books.available = 1 OR (books.created_at >= '2014-01-01 00:00:00' and books.created_at <= '2014-12-31 00:00:00')
```

Of course, these `LIKE '%...%'` queries won't achieve optimal performance, but
check out the section below on AttrSearchable's fulltext capabilities to
understand how the resulting queries can be optimized.

As `Book.search(...)` returns an `ActiveRecord::Relation`, you are free to pre-
or post-process the search results in every possible way:

```ruby
Book.where(:available => true).search("<NAME>").order("books.id desc").paginate(:page => params[:page])
```

## Fulltext index capabilities

By default, i.e. if you don't tell AttrSearchable about your fulltext indices,
AttrSearchable will use `LIKE '%...%'` queries. Unfortunately, unless you
create a [trigram index](http://www.postgresql.org/docs/9.1/static/pgtrgm.html)
(postgres only), theses queries can not use SQL indices, such that every row
needs to be scanned by your RDBMS when you search for `Book.search("Harry
Potter")` or similar. To avoid the penalty of `LIKE` queries, AttrSearchable
can exploit the fulltext index capabilities of MySQL and PostgreSQL. To use
already existing fulltext indices, simply tell AttrSearchable to use them via:

```ruby
class Book < ActiveRecord::Base
  # ...

  attr_searchable_options :title, :type => :fulltext
  attr_searchable_options :author, :type => :fulltext

  # ...
end
```

AttrSearchable will then transparently change its SQL queries for the
attributes having fulltext indices to:

```ruby
Book.search("<NAME>")
# MySQL: ... WHERE (MATCH(books.title) AGAINST('+Harry' IN BOOLEAN MODE) OR MATCH(books.author) AGAINST('+Harry' IN BOOLEAN MODE)) AND (MATCH(books.title) AGAINST ('+Potter' IN BOOLEAN MODE) OR MATCH(books.author) AGAINST('+Potter' IN BOOLEAN MODE))
# PostgreSQL: ... WHERE (to_tsvector('simple', books.title) @@ to_tsquery('simple', 'Harry') OR to_tsvector('simple', books.author) @@ to_tsquery('simple', 'Harry')) AND (to_tsvector('simple', books.title) @@ to_tsquery('simple', 'Potter') OR to_tsvector('simple', books.author) @@ to_tsquery('simple', 'Potter'))
```

Obviously, theses queries won't always return the same results as wildcard
`LIKE` queries, because we search for words instead of sub-strings. However,
fulltext indices will usually of course provide better performance.

Moreover, the query above is not yet perfect. To improve it even more,
AttrSearchable tries to optimize the queries to make optimal use of fulltext
indices while still allowing to mix them with non-fulltext attributes. To
improve queries, the first thing you want to do is to specify a default field
to search in, such that AttrSearchable must no longer search within all fields:

```ruby
attr_searchable :all => [:author, :title]

attr_searchable_options :all, :type => :fulltext, :default => true

# Use :default => true to explicitly enable fields as default fields (whitelist approach)
# Use :default => false to explicitly disable fields as default fields (blacklist approach)
```

Now AttrSearchable can optimize the following, not yet optimal query:

```ruby
BookSearch("Rowling OR Tolkien stock > 1")
# MySQL: ... WHERE ((MATCH(books.author) AGAINST('+Rowling' IN BOOLEAN MODE) OR MATCH(books.title) AGAINST('+Rowling' IN BOOLEAN MODE)) OR (MATCH(books.author) AGAINST('+Tolkien' IN BOOLEAN MODE) OR MATCH(books.title) AGAINST('+Tolkien' IN BOOLEAN MODE))) AND books.stock > 1
# PostgreSQL: ... WHERE ((to_tsvector('simple', books.author) @@ to_tsquery('simple', 'Rowling') OR to_tsvector('simple', books.title) @@ to_tsquery('simple', 'Rowling')) OR (to_tsvector('simple', books.author) @@ to_tsquery('simple', 'Tolkien') OR to_tsvector('simple', books.title) @@ to_tsquery('simple', 'Tolkien'))) AND books.stock > 1
```

to the following, more performant query:

```ruby
BookSearch("Rowling OR Tolkien stock > 1")
# MySQL: ... WHERE MATCH(books.author, books.title) AGAINST('Rowling Tolkien' IN BOOLEAN MODE) AND books.stock > 1
# PostgreSQL: ... WHERE to_tsvector('simple', books.author || ' ' || books.title) @@ to_tsquery('simple', 'Rowling | Tokien') and books.stock > 1
```

Other queries will be optimized in a similar way, such that AttrSearchable
tries to minimize the fultext constraints within a query, namely `MATCH()
AGAINST()` for MySQL and `to_tsvector() @@ to_tsquery()` for PostgreSQL.

```ruby
BookSearch("(Rowling -Potter) OR Tolkien")
# MySQL: ... WHERE MATCH(books.author, books.title) AGAINST('(+Rowling -Potter) Tolkien' IN BOOLEAN MODE)
{'fraction_non_alphanumeric': 0.13717339667458434, 'fraction_numerical': 0.007785695434151491, 'mean_word_length': 5.242586490939044, 'pattern_counts': {'":': 0, '<': 25, '<?xml version=': 0, '>': 97, 'https://': 14, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2907123', 'n_tokens_mistral': 4944, 'n_tokens_neox': 4607, 'n_words': 1593}