Search problems with joining two models/tables.

rubyonrails-talk
1

Hi to all,

I am having problems getting a search to work. I have a search form with one text field for search linking to missing_children_controller.rb. This text field gets the name of a child to find matches.

What I'm trying to do is to get the search parameter (which would be the name of the child) to first search through the children table in the database to pull any matches. Then I feed the result of the matches (@children.id) to see if any of the previous matches from the search are actually missing children (in the missing_children table in the database).

Models Associations: Child has_many :missing_children

MissingChild belongs_to :child

Database Tables: children

2

Tony Tony wrote:

Models Associations: Child has_many :missing_children

MissingChild belongs_to :child

Shouldn't the missing-ness of a child be a boolean in the child table?

missing_children_controller.rb [code] @children = Child.find(:all, :conditions => ["name LIKE ?", "%#{params[:search]}%"])

Even if Rails blogs often show run-on lines, you should not follow their example. Breaking things out into separate statements often makes them clearer:

   name_match = ['children.name LIKE ?', "%#{params[:search]}%"]    children = Child.find(:all, :conditions => name_match)

Also, don't make children a @data member of the current object until you really need that. And I did not use "" on any string which did not need any "" abilities.

Now children is an array of ids, so extract the ids like this:

   ids = children.map(&:id)

And pull the matching missing children:

   matching_ids = ['child_id in (?)', ids]    @missing_children = MissingChild.find(:all, :conditions => matching_ids)

I wrote that just to illustrate the in(?) trick. We can simplify it further:

   @missing_children = MissingChild.find_all_by_child_id(ids)

Now we will simplify again:

   MissingChild.find(:all, :include => :child, :conditions => name_match)

I think I could keep going. And to think that one Joel Spolsky had the nerve to claim ActiveRecord simply didn't understand relational databases! I don't think it was ActiveRecord doing the misunderstanding...

3

There's :joins which will just join the associations, which will save activerecord actually instantiating all the child objects.

Fred

4

Frederick Cheung wrote:

There's :joins which will just join the associations, which will save activerecord actually instantiating all the child objects.

Thanks! We have been wondering how to bypass eager-loading and still get elaborate queries. We will try that!

5

Phlip wrote:

Tony Tony wrote:

Models Associations: Child has_many :missing_children

MissingChild belongs_to :child

Shouldn't the missing-ness of a child be a boolean in the child table?

missing_children_controller.rb [code] @children = Child.find(:all, :conditions => ["name LIKE ?", "%#{params[:search]}%"])

Even if Rails blogs often show run-on lines, you should not follow their example. Breaking things out into separate statements often makes them clearer:

   name_match = ['children.name LIKE ?', "%#{params[:search]}%"]    children = Child.find(:all, :conditions => name_match)

Also, don't make children a @data member of the current object until you really need that. And I did not use "" on any string which did not need any "" abilities.

Now children is an array of ids, so extract the ids like this:

   ids = children.map(&:id)

And pull the matching missing children:

   matching_ids = ['child_id in (?)', ids]    @missing_children = MissingChild.find(:all, :conditions => matching_ids)

I wrote that just to illustrate the in(?) trick. We can simplify it further:

   @missing_children = MissingChild.find_all_by_child_id(ids)

Now we will simplify again:

   MissingChild.find(:all, :include => :child, :conditions => name_match)

I think I could keep going. And to think that one Joel Spolsky had the nerve to claim ActiveRecord simply didn't understand relational databases! I don't think it was ActiveRecord doing the misunderstanding...

--    Phlip

Phlip,

Thank you so much! Worked just as I wanted! Learned a couple more things too.

I wanted to ask you... is it better to do a boolean in the child table? I only plan to store the following three values: Lost, Found, and Safe. I currently have a table for the lost and found, if the child id isn't in either than the child is safe.

Thanks again! -Tony

6

Tony Tony wrote:

I wanted to ask you... is it better to do a boolean in the child table?

That is a design question. The strict answer, for _only_ the features you listed, is an integer, 1,2,3, that index into an array of read-only strings in your code - %w(Error Lost Found Safe).

However, if you had any more features or business rules, they might change which design is optimal. The best bet is to write your code as simply as possible, and write unit tests for every single feature - following Test Driven Development. Then when you discover more business rules, if they contradict your current design, you can write a database migration to upgrade the data, then write unit tests for the new features. The combination of simple design, test, and migrations makes your project "agile".

Guessing ahead of time what design you might someday need will invariably fill your code up with cruft.

I only plan to store the following three values: Lost, Found, and Safe. I currently have a table for the lost and found, if the child id isn't in either than the child is safe.

Don't abuse entire tables just to store states! But as soon as the combination of Child and Found has some new variable, that Children and Founds don't have separately, then you might need that table to store that variable.