how to search through associations in rails 4

Question

I want to get all profiles whom subject names will match with search keyword. right now i am loading all profiles. i need to know how do i achieve it. Any help is much appreciated.

Profile.rb

has_many :categorizations
has_many :subjects, through: :categorizations

Subject.rb

has_many :categorizations
has_many :profiles, through: :categorizations

Categorization.rb

belongs_to :profile
belongs_to :subject

views/search/index.html.erb

# search form
<%= form_tag search_index_path, :method => 'get' do %>
  <%= text_field_tag :search, params[:search] %>
  <%= submit_tag "search", :name => nil %>
<% end %>

# search results
<% @profiles.each do |profile| %>
  <%= profile.name %>
<% end %>

search_controller.rb

def index
  @profiles = Profile.with_translations('en').all
end

Answer 1

Something to add to this is the idea of full text searching with Rails. If you're performing searches, you need to be aware that you're actually performing a full text "search" query in your DB, which is different depending on the SQL engine you use

---

MYSQL Full Text Search

The LIKE %{search}% mechanism is MYSQL's very basic full text search feature, and basically looks for your target query inside the whole record in your DB. This means that if your query is this:

SELECT * FROM `products` WHERE `name` LIKE '%alligator%'

MYSQL will basically look through the entire "name" record for any reference to your query. Results will be based on whether your record has the word "alligator" in any part of it. This reference explains some more about this

---

PostgreSQL Full Text Search

The reason I wrote this post is because PSQL actually does this differently, and therefore the query you have been provided will only work for MYSQL. PSQL has a lot of different functions to handle full text searching, but since we use Heroku, we managed to use the Textacular gem to get it all working properly

Here are some ways PSQL handles full text searching:

Full text searching in PostgreSQL is based on the match operator @@, which returns true if a tsvector (document) matches a tsquery (query). It doesn't matter which data type is written first:

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat &
rat'::tsquery;  ?column?
----------  t

SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat
rat'::tsvector;  ?column?
----------  f 

As the above example suggests, a tsquery is not just raw text, any more than a tsvector > is. A tsquery contains search terms, which must be already-normalized lexemes, and may combine multiple terms using AND, OR, and NOT operators. (For details see Section 8.11.) There are functions to_tsquery and plainto_tsquery that are helpful in converting user-written text into a proper tsquery, for example by normalizing words appearing in the text. Similarly, to_tsvector is used to parse and normalize a document string. So in practice a text search match would look more like this:

SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');  ?column? 
----------  t Observe that this match would not succeed if written as

SELECT 'fat cats ate fat rats'::tsvector @@ to_tsquery('fat & rat'); ?column? ---------- f

---

Full Text Search Software

Full text searching is inherently quite expensive on the DB, especially if you have a lot of data to search through. That's why solutions like sunspot solr or sphinx exist -- to provide a way to both index & search the data you have

If your application becomes quite popular, you may wish to invest into one of the full text search systems, such as these demonstrated by Heroku:

Answer 2

@profiles = Subject.where("name LIKE ?", "%#{params[:search]}%").map(&:profiles)

Answer 3

Check if this works.

@profiles = Profile.joins(:subjects).where("subject.name like '%?%'",params[:search])