CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3postgresqlfull-text-search
Dl33ter
Dl33ter

Reputation: 85

Rails - Postgresql - Full text search - Railscasts 343

I'm following the Railscasts #343 Full-Text Search in PostgreSQL, and at the beggining I get a problem.

I replace this line in my modele :

where("name ilike :q or content ilike :q", q: "%#{query}%")

by

where("name @@ :q or content @@ :q", q: query)

But, it still don't perform the search as he can in the railscasts. If in the name I have "FOO", and in the content "BAR", and if I search "FOO BAR" the result is empty. (Like with the old code and the "ilike" function)

The SQL request look like that :

* FROM "posts" where (name @@ 'foo bar' or content @@ 'foo bar')

I'm using PostgreSQL 9.2.2 , Rails 3.2.6 and Ruby 1.9.3p125 on Windows 7 x64.

Thanks by advance.

EDIT

My Gem file :

    source 'https://rubygems.org'

gem 'rails', '3.2.6'

gem 'pg'
gem "ransack"


group :assets do
  gem 'sass-rails',   '~> 3.2.3'
  gem 'coffee-rails', '~> 3.2.1'


  gem 'uglifier', '>= 1.0.3'
end

gem 'jquery-rails'

I removed all commented line.

PS: Just to be clear, before I add "ransack", I had the same problem.

Upvotes: 0

Views: 1027

Answers (1)

Jesse Wolgamott
Jesse Wolgamott

Reputation: 40277

In Ryan's example, the article's have to have both superman AND character to return in the results. It's not an OR in the way I think you want it to be. That's where full text searching comes in (later in the episode)

I just followed the railscast code nearly-exactly*.

This code

class Article < ActiveRecord::Base
  attr_accessible :content, :title

  def self.text_search(query)
    if query.present?
      where("title @@ :q or content @@ :q", q: query)
    else
      scoped
    end
  end
end

Generates this SQL

SELECT "articles".* FROM "articles" WHERE (title @@ 'because' or content @@ 'because')

which works and returns articles that have 'because' in either the title or content

  • I used 'title' instead of name

Upvotes: 2

Related Questions