Search problems with joining two models/tables.

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

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...

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

Fred

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!

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

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.