Rails - Postgresql - Full text search - Railscasts 343

Question

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.

I get: * FROM "posts" where (name @@ 'foo|bar' or content @@ 'foo|bar'). — Dl33ter
– Dl33ter, Commented Feb 4, 2013 at 21:49

Jesse Wolgamott · Accepted Answer · 2013-02-05 02:57:15Z

2

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

answered Feb 5, 2013 at 2:57

Jesse Wolgamott

40.3k4 gold badges86 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dl33ter Over a year ago

I get exacly the same SQL request, but it search if 'Posts' have 'FOO+BAR' in title or 'FOO+BAR in the content. It's not doing what I expect which is : search if 'FOO or BAR is present in title or content'. Like the beggining of the screecast when he replace 'ilike' by '@@'.

Jesse Wolgamott Over a year ago

You should probably create a github repo with your code, and the data created via db/seeds.rb. Then put the exact query you're using and the results you're expecting. It's almost certainly that your expectations for this intermediate step in the tutorial are not in sync with what Ryan said it would do.

Collectives™ on Stack Overflow

Rails - Postgresql - Full text search - Railscasts 343

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related