How can i search my Rails database?

May 26, 2008 8:12 AM

I'm so close to finishing my new database project, but I'm bumping up against my personal ceiling. Can anyone help me determine how to search my Rails project?

Background Server Information:
- Instant Rails 2.0
- Ruby 1.8.6 Patch Level 111
- Upgrades Rails to 2.0.2
- MySQL 5.0
- Ferret 0.11.6
- Acts As Ferret 0.4.3

I've got what I think is a simple database setup, but cannot figure out how to search the project. I've got my tables created, forms are accepting data, but I can't seem to search.

This is the last piece of the project, so I'm itching for an answer. Any help would be greatly appreciated! I'll try to err on the side of 'too much' information.

Tables:
- Clients (ID, username, business_name, ... , Tradename_ID)
- Tradenames (ID and title)
- Words (ID, term, and Tradename_ID)

Links:
- Clients have a Tradename_ID
- Words have a Tradename_ID

Relations:
- Each Client has a Tradename (has_one :tradenames)
- Each Word has a Tradename (has_one :tradenames)
- Each Tradename has many Clients (has_many :clients)
- Each Tradename has many Words (has_many :clients)

I would like to have a search box that searches the entire project. It should return results that meet any of the following criteria:
- If the search term returns a Tradename, return all Clients with that Tradename ID
- If the search term returns a Word, return all Clients with that linked Tradename ID
- If the search term doesn't match a Tradename or Word, then search the Clients business_name for a match. If found, return any Clients with a matching business_name

Currently, I've got another model called 'Community' that is acting as a hub for all the information. The current search model (from app\models\community.rb) looks like this:

  def search
	@title = "Search"
	if params[:q]
		query = params[:q]
		# First find the user hits...
		@clients = Client.find_by_contents(query, :limit => :all)
		# ...then the subhits.
		tradenames = Tradename.find_by_contents(query, :limit => :all)
		words = Word.find_by_contents(query, :limit => :all)

		# Now combine into one list of distinct users sorted by last name.
		hits = tradenames + words
		@clients.concat(hits.collect { |hit| hit.clients }).uniq!        
		@clients = @clients.sort { |client| client.business_name }
	end
  end

This is the code that is doing the searching (in app\views\community\_search_form.rhtml):

<% form_tag({ :action => "search", :controller => "community" }, :method => "get") do %>
<fieldset>
	<legend>Search</legend> 
	<dl>
		<dt><label for="q">Search for:</label></dt>
		<dd><%= text_field_tag "q", params[:q] %></dd>
    	</dl>
</fieldset>
<input id="submit" type="submit" value="Search" />
<% end %>

Any ideas? Should I be joining all of these into a single table? Some sort of recursive search? If so, how?

(I'm Leia in the 'Help me Obi-Wan Kenobi, you're my only hope' analogy.)

posted by jmevius to computers & internet (13 answers total)

Well, all that search code should be in a controller, probably the application controller since you want to search many models.

Since you're using ferret and that's what it's for, make sure you've indexed all the fields you want to search in each model, then use this line to search:

@results = Client.find_by_contents("jemen", :models => :all)

It should search across all the models you've added indexes on. Good luck!
posted by cdmwebs at 9:03 AM on May 26, 2008

Specifically, your problem is that the params hash isn't available inside of model classes.
posted by voidcontext at 9:35 AM on May 26, 2008

Could you explain the 'indexed all fields' part? I've created something in app\models\client.rb (for example) called:

acts_as_ferret :fields => ['business_name', 'screen_name']

Because I don't want to index things like the password. Is that what you mean by indexing? When I use the command (and look at my environment window), I see something that says:

ENV["FERRET_USE_LOCAL_INDEX"] is nil, looks like we are not the server
Will use local index.

Any ideas?
posted by jmevius at 9:47 AM on May 26, 2008

Yes, that's what I meant.

That warning just means that ferret is using the local server instead of a DRb server. It's fine for your development box - just disregard it for now.

You can also specify the models you want to search, instead of saying :all, you could say:

@results = Client.find_by_contents("jemen", :models => [Client, Tradename, Word])
posted by cdmwebs at 9:58 AM on May 26, 2008

Wow -- I think I'm really close. This is what I have as a search expression now:

  def search
	@title = "Search"
	if params[:q]
		query = params[:q]
		# First find the user hits...
		@results1 = Tradename.find_by_contents(query, :models => [Tradename, Client], :limit => :all)
		if @results1.empty? 
			@results2 = Word.find_by_contents(query, :models => [Word, Tradename, Client], :limit => :all)
				if @results2.empty?
					@results = Client.find_by_contents(query, :models => [Client], :limit => :all)
				else
					@results = @results2
				end
		else
			@results = @results1
		end	
	end
  end

And it seems to be pulling the correct number of listings (querying all tables appropriately). Now, I'm just stuck on the display side of things.

I have this sort of code for table display:

<% if @results and not @results.empty? %>
<table class="database_dump">
	<tr class="header">
		<th class="name">Business Name</th>
	</tr>
	<% @results.each do |result| %>
		<tr class="<%= cycle('odd', 'even') %>">
			<td class="name"><%= @results.id %></td>
		</tr>
	<% end %>
</table>
<% end %>

It's giving me the proper *number* of results, but not the display I'm looking for. I can't figure out how to construct the term that says "for the results found in the query, give me the information from the clients table (specifically client.business_name, client.screen_name)".

I'm so close! Thanks so far for all of the help.
posted by jmevius at 12:06 PM on May 26, 2008

That's because you're trying to use the result set (@results) as the instance (result). It should look like this:

http://pastie.caboo.se/203575
posted by cdmwebs at 12:25 PM on May 26, 2008

Hmm, this is really close. In the three scenarios for search, this returns the correct values whenever the query matches the Clients table. If the correct match comes from Tradenames or Words, I get an error that says:

undefined method `business_name' for #<Tradename id: 1, title: "Plumber">

or

undefined method `business_name' for #<Word id: 3, tradename_id: 1, term: "pipe">

This is returning the correct tradename_id for both queries (that is huge!). But, I'd like to return all results from Clients that have the same Tradename_id. I'm almost there!
posted by jmevius at 1:45 PM on May 26, 2008

I might have to mock up a test, but I guess you could write an ugly case statement to see which model was returned and then use the proper field.

case result
  when Client
    # client code here
  when Tradename
     # tradename code here
  when Word
      # word code here
end

Please note that this is bad form, but it will probably get you working until someone can help further or you figure it out.
posted by cdmwebs at 6:32 PM on May 26, 2008

cdmwebs - I'm not completely sure about what the code above meant. My lack of knowledge on this one is massive, obviously.

After playing with this thing after a late barbecue, I think I'm pretty close. Now, I've got the Search feeding into three blocks of code, depending on where the results come from.

One block works -- it looks like this:

<% if @results3 and not @results3.empty? %>
	<% @results.each do |result| %>
		<tr class="<%= cycle('odd', 'even') %>">
			<td class="name"><%= result.business_name %></td>
			<td class="profile"><%= link_to result.screen_name, profile_url(:screen_name => result.screen_name) %></td>
			<td class="phone"><%= result.contact_phone %></td>
			<td class="zip"><%= result.address_zip %></td>
		</tr>
	<% end %>
<% end %>

This block says, 'if the search term is found in Clients, return the following results'. It works great (I'm sure an actual Rails code expert would be horrified at the code that powers it).

I've got another block that says 'if the search term is found in Tradenames, return the Tradename ID'. It looks like this:

<% if @results1 and not @results1.empty? %>
	<% @results.each do |result| %>
		<tr class="<%= cycle('odd', 'even') %>">
			<td class="name"><%= result.title %></td>
			<td class="profile"><%= result.id %></td>
			<td class="phone"> </td>
			<td class="zip"> </td>
		</tr>
	<% end %>
<% end %>

What I would like it to do is another query. If a Tradename ID is returned (in result.id), then search the Clients table for all the clients that have the same Tradename ID.

All of this code is stored in app\views\community\_client_table.rhtml

Any ideas? This is the last step to finishing my demo -- I'm so close.
posted by jmevius at 4:53 AM on May 27, 2008

Can you make a pastie of the entire view?
posted by cdmwebs at 3:15 PM on May 27, 2008

Sure thing! I'll make a handful for completeness-sake.

Views:
app\views\community\_client_table.rhtml - search results code
app\views\community\search.rhtml (this one isn't too exciting)
app\views\community\_search_form.rhtml

Controllers:
app\controllers\community_controller.rb - def search code

Models:
app\models\client.rb
app\models\word.rb
app\models\tradename.rb
app\models\community.rb

Table Setups:
db\migrate\001_create_tradenames.rb
db\migrate\002_create_clients.rb
db\migrate\003_create_words.rb

Sorry if I got a little pastie-crazy.
posted by jmevius at 1:23 PM on May 28, 2008

To clarify, on the app\views\community\_client_table.rhtml view, the @results3 section is working as intended. @results1 & @results2 are returning a correct tradename_id for the query. I'd like to then use that tradename_id to query the clients table and return a list of all clients that have a matching tradename_id.
posted by jmevius at 4:17 PM on May 28, 2008

Wow - like 900 things have changed with acts_as_ferret.

I'm working on your app, but check out this link for a description. He's got a sample application that does exactly what you want to do, also.
posted by cdmwebs at 6:20 PM on May 28, 2008