Search problems with joining two models/tables

Rails
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

id:int
name:string

missing_children

id:int
child_id:int

missing_children_controller.rb

@children = Child.find(:all, :conditions => ["name LIKE ?",
"%#{params[:search]}%"])
@missing_children = MissingChild.find(:all, :conditions => ["child_id =
?", @children.id])

The above code generates a random id from @children.id (for example:
18927530). When it should generate the actual child id. However if I
replease @children.id in the above code to 1 (which is an actual child
id in the children database and also listed in the child_id in the
missing_children database, it DOES work.

Is this code correct? Maybe there’s a better way to do this?

Any ideas on why this is not working? I’m pretty new to Ruby on Rails
but I think this database structure is the right way to go. In PHP I
normally wouldn’t have a seperate database for missing_children, I would
just add a status:string to the children database and put fill it with
“Lost”.

Anyway, I have a feeling I’m doing something wrong so any input is
appreciated. Thanks in advance!
-Tony

2

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

3

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

Fred

4

Frederick C. 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 T. 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 T. 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.

–
Phlip