# Search problems with joining two models/tables

**URL:** <https://www.ruby-forum.com/t/search-problems-with-joining-two-models-tables/139828>\
**Category:** Rails\
**Created:** [June 7, 2008, 7:33pm UTC](https://www.ruby-forum.com/t/search-problems-with-joining-two-models-tables/139828 "2008-06-07T19:33:34Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![bagadoughnuts](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/71c47a/32.png) [@bagadoughnuts](https://www.ruby-forum.com/u/bagadoughnuts)\
**Post date:** [June 7, 2008, 7:33pm UTC](https://www.ruby-forum.com/t/search-problems-with-joining-two-models-tables/139828/1 "2008-06-07T19:33:34Z")

</div>

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

```auto
@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

---

<div class="post-metadata">

**Author:** ![bagadoughnuts](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/71c47a/32.png) [@bagadoughnuts](https://www.ruby-forum.com/u/bagadoughnuts)\
**Post date:** [June 7, 2008, 8:16pm UTC](https://www.ruby-forum.com/t/search-problems-with-joining-two-models-tables/139828/2 "2008-06-07T20:16:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bagadoughnuts](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/71c47a/32.png) [@bagadoughnuts](https://www.ruby-forum.com/u/bagadoughnuts)\
**Post date:** [June 7, 2008, 8:46pm UTC](https://www.ruby-forum.com/t/search-problems-with-joining-two-models-tables/139828/3 "2008-06-07T20:46:14Z")

</div>

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

Fred

---

<div class="post-metadata">

**Author:** ![bagadoughnuts](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/71c47a/32.png) [@bagadoughnuts](https://www.ruby-forum.com/u/bagadoughnuts)\
**Post date:** [June 7, 2008, 8:52pm UTC](https://www.ruby-forum.com/t/search-problems-with-joining-two-models-tables/139828/4 "2008-06-07T20:52:09Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![bagadoughnuts](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/71c47a/32.png) [@bagadoughnuts](https://www.ruby-forum.com/u/bagadoughnuts)\
**Post date:** [June 7, 2008, 9:05pm UTC](https://www.ruby-forum.com/t/search-problems-with-joining-two-models-tables/139828/5 "2008-06-07T21:05:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bagadoughnuts](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/71c47a/32.png) [@bagadoughnuts](https://www.ruby-forum.com/u/bagadoughnuts)\
**Post date:** [June 7, 2008, 9:25pm UTC](https://www.ruby-forum.com/t/search-problems-with-joining-two-models-tables/139828/6 "2008-06-07T21:25:06Z")

</div>

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