# Need help with my sql

**URL:** <https://www.ruby-forum.com/t/need-help-with-my-sql/130715>\
**Category:** Rails\
**Created:** [March 2, 2008, 11:50am UTC](https://www.ruby-forum.com/t/need-help-with-my-sql/130715 "2008-03-02T11:50:06Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![megax](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/94ad74/32.png) [@megax](https://www.ruby-forum.com/u/megax)\
**Post date:** [March 2, 2008, 11:50am UTC](https://www.ruby-forum.com/t/need-help-with-my-sql/130715/1 "2008-03-02T11:50:06Z")

</div>

Hi all

Im new to ruby on rails but trying to learn fast however i’ve hit a  
roadblock. Im trying to do an sql statement in a view to get values from  
a table however I have not been able to get get back just the values, I  
seem to get the column names and the values as shown below.

seller\_rating8advert\_rating10commentGreat ad for that item of yours.

The line I am using to do this is

\<%= rating = Product.connection.select\_all("select comment,  
seller\_rating, advert\_rating from ratings ")%\>

I was also hoping to use the rating bit to print out the the values on  
their own line for a row and then have a line and then print out the  
next row as before.

e.g

seller ratting: 8  
advert\_rating: 10  
comment: Great ad for that item of yours.

---

<div class="post-metadata">

**Author:** ![megax](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/94ad74/32.png) [@megax](https://www.ruby-forum.com/u/megax)\
**Post date:** [March 3, 2008, 11:39am UTC](https://www.ruby-forum.com/t/need-help-with-my-sql/130715/2 "2008-03-03T11:39:18Z")

</div>

Mark B. wrote:

> Alan Red wrote:
> 
> > \<%= rating = Product.connection.select\_all("select comment,  
> > seller\_rating, advert\_rating from ratings ")%\>
> 
> Since you are just doing a database query and not getting any model  
> objects, you will get an array of hashes as the result. Each hash will  
> map the column name from your query to its value.
> 
> Your “\<%=” then means output this value. When you ask Ruby to print a  
> hash, it just rubs together keys and values as you are seeing.
> 
> Firstly, you have model specific code in your view. If the ratings  
> table changes, you will need to hunt through your code for things like  
> this and change them.
> 
> If you have a ratings table, then do you not have a Ratings model? Why  
> use another model (Product) to look up a Rating? It looks like you  
> actually want your lookup to be:
> 
> Rating.find(:all)
> 
> Much simpler and no SQL.
> 
> Then you can go through then printing them out something like this:
> 
> \<% Rating.find(:all) do |rating| %\>  
> Seller rating: \<% rating.seller\_rating %\>  
>   
> Advert rating: \<% rating.advert\_rating %\>  
>   
> Comment: \<% rating.comment %\>
> 
> * * *
> \<% end %\>
> 
> If you do find yourself needing to run SQL to get some results, decide  
> what model is best associated with the query and add a method to that  
> model to get the data. Then elsewhere, you just need to call that  
> method and use the results.

I should have explained what product is, I have two tables product has  
product details and rating holds details for the product (users will  
rate products). I want them to be able to move to a page and the comment  
related to that pages item will appear.

e.g

Product’s details

Then comments from the rating table related will appear here.

Im only getting started with rails so I don’t know much about its ins  
and outs. Im not sur how to do methods in models and the databases,  
along with pages were created using scaffold.

---

<div class="post-metadata">

**Author:** ![megax](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/94ad74/32.png) [@megax](https://www.ruby-forum.com/u/megax)\
**Post date:** [March 2, 2008, 3:46pm UTC](https://www.ruby-forum.com/t/need-help-with-my-sql/130715/3 "2008-03-02T15:46:29Z")

</div>

Alan Red wrote:

> \<%= rating = Product.connection.select\_all("select comment,  
> seller\_rating, advert\_rating from ratings ")%\>

Since you are just doing a database query and not getting any model  
objects, you will get an array of hashes as the result. Each hash will  
map the column name from your query to its value.

Your “\<%=” then means output this value. When you ask Ruby to print a  
hash, it just rubs together keys and values as you are seeing.

Firstly, you have model specific code in your view. If the ratings  
table changes, you will need to hunt through your code for things like  
this and change them.

If you have a ratings table, then do you not have a Ratings model? Why  
use another model (Product) to look up a Rating? It looks like you  
actually want your lookup to be:

Rating.find(:all)

Much simpler and no SQL.

Then you can go through then printing them out something like this:

\<% Rating.find(:all) do |rating| %\>  
Seller rating: \<% rating.seller\_rating %\>  
  
Advert rating: \<% rating.advert\_rating %\>  
  
Comment: \<% rating.comment %\>

* * *
\<% end %\>

If you do find yourself needing to run SQL to get some results, decide  
what model is best associated with the query and add a method to that  
model to get the data. Then elsewhere, you just need to call that  
method and use the results.

---

<div class="post-metadata">

**Author:** ![megax](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/94ad74/32.png) [@megax](https://www.ruby-forum.com/u/megax)\
**Post date:** [March 3, 2008, 1:11pm UTC](https://www.ruby-forum.com/t/need-help-with-my-sql/130715/4 "2008-03-03T13:11:46Z")

</div>

Alan Red wrote:

> I should have explained what product is, I have two tables product has  
> product details and rating holds details for the product (users will  
> rate products). I want them to be able to move to a page and the comment  
> related to that pages item will appear.

So your models are like this:

class Product \< ActiveRecord::Base  
has\_many :ratings  
end

class Rating \< ActiveRecord::Base  
belongs\_to :product  
end

?

In your Product show method, you are probably doing something like:

@product = Product.find(params[:id])

?

If so, then update this to:

@product = Product.find(params[:id], :include =\> :ratings)

Then, in your view, you can display your Product object (@product), then  
to display the ratings for that product something like:

\<% @product.ratings.each do |rating| %\>  
Seller rating: \<% rating.seller\_rating %\>  
  
Advert rating: \<% rating.advert\_rating %\>  
  
Comment: \<% rating.comment %\>

* * *
\<% end %\>

Is this more what you have in mind?

If not, can you explain more about your class definitions, tables, how  
they are related and then the output you want in those terms?