# Problems select correct records from 2 tables

**URL:** <https://www.ruby-forum.com/t/problems-select-correct-records-from-2-tables/89736>\
**Category:** Rails\
**Created:** [March 22, 2007, 12:40pm UTC](https://www.ruby-forum.com/t/problems-select-correct-records-from-2-tables/89736 "2007-03-22T12:40:15Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![webdevotion](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/48db29/32.png) [@webdevotion](https://www.ruby-forum.com/u/webdevotion)\
**Post date:** [March 22, 2007, 12:40pm UTC](https://www.ruby-forum.com/t/problems-select-correct-records-from-2-tables/89736/1 "2007-03-22T12:40:15Z")

</div>

Hello,

I have 2 tables: news and comments.

news has many comments and comment belongs to article

On the show action I want to show all the comments that  
belong to a news item, excluding the ones that are not approved yet.  
It’s the excluding part that’s causing me problems.

example sql (from the head, not tested):

select news.id,news.title,news.article,comments.message, …  
from news,comments  
where comments.news\_id = news.id  
AND approved = 1

How do I set this up in the models ?

---

<div class="post-metadata">

**Author:** ![webdevotion](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/48db29/32.png) [@webdevotion](https://www.ruby-forum.com/u/webdevotion)\
**Post date:** [March 22, 2007, 1:13pm UTC](https://www.ruby-forum.com/t/problems-select-correct-records-from-2-tables/89736/2 "2007-03-22T13:13:13Z")

</div>

Is this the correct solution to my problem ?  
Or is there a better way?

in my news controller:

def show  
@news = News.find( params[:id] )  
@comments = Comment.find(:all,:conditions=\>"news\_id = " +  
params[:id].to\_s )  
end

Mister T. wrote:

> Hello,
> 
> I have 2 tables: news and comments.
> 
> news has many comments and comment belongs to article
> 
> On the show action I want to show all the comments that  
> belong to a news item, excluding the ones that are not approved yet.  
> It’s the excluding part that’s causing me problems.
> 
> example sql (from the head, not tested):
> 
> select news.id,news.title,news.article,comments.message, …  
> from news,comments  
> where comments.news\_id = news.id  
> AND approved = 1
> 
> How do I set this up in the models ?

---

<div class="post-metadata">

**Author:** ![webdevotion](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/48db29/32.png) [@webdevotion](https://www.ruby-forum.com/u/webdevotion)\
**Post date:** [March 22, 2007, 2:07pm UTC](https://www.ruby-forum.com/t/problems-select-correct-records-from-2-tables/89736/3 "2007-03-22T14:07:28Z")

</div>

Thanks Morten,

But this does not get rid of the comments that are not approved yet.  
It shows all the comments.

I only want to show the comments that are approved.

> On Mar 22, 1:13 pm, Mister T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
> wrote:
> 
> > 
> 
> def show  
> @news = News.find(params[:id])  
> end
> 
> And then in your view, eg.:
> 
> \<% @news.comments.each do |comment| %\>
> 
> \<%= comment.message %\>
> 
> * * *
> \<% end %\>

---

<div class="post-metadata">

**Author:** ![webdevotion](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/48db29/32.png) [@webdevotion](https://www.ruby-forum.com/u/webdevotion)\
**Post date:** [March 22, 2007, 1:59pm UTC](https://www.ruby-forum.com/t/problems-select-correct-records-from-2-tables/89736/4 "2007-03-22T13:59:26Z")

</div>

On Mar 22, 1:13 pm, Mister T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> 

def show  
@news = News.find(params[:id])  
end

And then in your view, eg.:

\<% @news.comments.each do |comment| %\>

\<%= comment.message %\>

* * *
\<% end %\>

---

<div class="post-metadata">

**Author:** ![webdevotion](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/48db29/32.png) [@webdevotion](https://www.ruby-forum.com/u/webdevotion)\
**Post date:** [March 22, 2007, 3:01pm UTC](https://www.ruby-forum.com/t/problems-select-correct-records-from-2-tables/89736/5 "2007-03-22T15:01:34Z")

</div>

On Mar 22, 2007, at 9:07 AM, Mister T. wrote:

> > def show  
> > \<% end %\>  
> > Assuming that your comments.approved column is a boolean, you can do  
> > this:

class News \< ActiveRecord::Base  
has\_many :approved\_comments, :class\_name =\> ‘Comment’, :conditions  
=\> [‘approved = ?’, true]  
end  
class Comment \< ActiveRecord::Base  
belongs\_to :news  
end

Doing it this way should be correct for any database (even when  
boolean is implemented by 0/1).

Then in your controller:

def show  
@news = News.find( params[:id] )  
@comments = @news.approved\_comments # or just do this in your view  
end

And your view:

\<% for comment in @news.approved\_comments -%\> - \<%= comment.body %\>
\<% end -%\>

Of course, your markup is whatever you prefer and I’ve guessed that  
the interesting piece from a comment is in a “body” column, but this  
should help you out.

-Rob

Rob B. [http://agileconsultingllc.com](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![webdevotion](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/48db29/32.png) [@webdevotion](https://www.ruby-forum.com/u/webdevotion)\
**Post date:** [March 22, 2007, 3:39pm UTC](https://www.ruby-forum.com/t/problems-select-correct-records-from-2-tables/89736/6 "2007-03-22T15:39:11Z")

</div>

I see. Then I would do this (you may need to change the condition):

class News  
has\_many :comments do  
def approved  
@approved\_comments ||= find(:all, :conditions =\> ‘is\_approved IS  
TRUE’)  
end  
end

#The rest of the class News…  
end

And in your view:

\<% @news.comments.approved.each do |comment| %\>

\<%= comment.message %\>

* * *
\<% end %\>

Br,

Morten

On Mar 22, 2:07 pm, Mister T. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![webdevotion](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/w/48db29/32.png) [@webdevotion](https://www.ruby-forum.com/u/webdevotion)\
**Post date:** [March 22, 2007, 5:06pm UTC](https://www.ruby-forum.com/t/problems-select-correct-records-from-2-tables/89736/7 "2007-03-22T17:06:14Z")

</div>

Thanks Rob and Morten!  
I’ve used Rob’s implementation as I read that one first 🙂  
It works like a charm.