# Find\_by\_sql, can't print results

**URL:** <https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207>\
**Category:** Rails\
**Created:** [June 23, 2006, 2:30am UTC](https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207 "2006-06-23T02:30:13Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jamie\_D](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ea5d25/32.png) [@Jamie\_D](https://www.ruby-forum.com/u/Jamie_D)\
**Post date:** [June 23, 2006, 2:30am UTC](https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207/1 "2006-06-23T02:30:13Z")

</div>

I am a little stuck with getting an sql result into my page. I want to  
run a select distinct that returns all of the different sections  
available and displays this as a form select. In my controller I have:

def edit  
@resource = Resource.find(params[:id])  
@sections = Resource.find\_by\_sql “SELECT DISTINCT  
resources.section FROM resources”  
end

And then in my view I have:

\<% for option in @sections %\>  
\<option value="\<%= section.section %\>"  
\<%= ’ selected’ if option.section == @resource.section %\>\>  
\<%= option.section %\>  
  
\<% end %\>

When I run this I get the following error:

You have a nil object when you didn’t expect it!  
You might have expected an instance of Array.  
The error occured while evaluating nil.each

> From what I can see I am doing the same as other examples. I have run  
> the SQL on its own to make sure it returns a result and it does.

Thanks  
Jamie

---

<div class="post-metadata">

**Author:** ![Jamie\_D](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ea5d25/32.png) [@Jamie\_D](https://www.ruby-forum.com/u/Jamie_D)\
**Post date:** [June 23, 2006, 3:58am UTC](https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207/2 "2006-06-23T03:58:54Z")

</div>

Jamie,

I don’t think your find\_by\_sql is giving you back what you think it is.  
You  
may want to look at what is contained in @sections using debug in your  
view:

\<%= debug( @sections ) %\>

I believe you’ll see its an array having a hash as its only member, and  
that  
hash has one key, probably something like ‘resources.section’. So to get  
at  
your list you’ll need something like:

@sections[0][‘resource.section’]

but the debug should make it clear.

Jeff

---

<div class="post-metadata">

**Author:** ![Jamie\_D](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ea5d25/32.png) [@Jamie\_D](https://www.ruby-forum.com/u/Jamie_D)\
**Post date:** [June 23, 2006, 4:33am UTC](https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207/3 "2006-06-23T04:33:55Z")

</div>

Jeff,

adding \<%= debug( @sections ) %\> to the view displays the following:

* * *

Nothing more, I tried \<%= debug( @sections[0] ) %\> as well which gives  
me a null error.

What does find\_by\_sql actually return?

Thanks  
Jamie

---

<div class="post-metadata">

**Author:** ![Jamie\_D](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ea5d25/32.png) [@Jamie\_D](https://www.ruby-forum.com/u/Jamie_D)\
**Post date:** [June 23, 2006, 5:15am UTC](https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207/4 "2006-06-23T05:15:32Z")

</div>

I was just wondering if I am going about this the correct way. In my  
database I have a “section” field which contains the section the  
record is filed under. What I want to do is populate a select form  
field with a list of available sections, hence the use of select  
distinct. Would there be a built in function within rails for doing  
this?

Jamie

---

<div class="post-metadata">

**Author:** ![Jamie\_D](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ea5d25/32.png) [@Jamie\_D](https://www.ruby-forum.com/u/Jamie_D)\
**Post date:** [June 23, 2006, 5:43am UTC](https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207/5 "2006-06-23T05:43:56Z")

</div>

Jamie D wrote:

> I was just wondering if I am going about this the correct way. In my  
> database I have a “section” field which contains the section the  
> record is filed under. What I want to do is populate a select form  
> field with a list of available sections, hence the use of select  
> distinct. Would there be a built in function within rails for doing  
> this?
> 
> Jamie

Array has a method ‘uniq’:

[1,2,1].uniq # [1,2]

---

<div class="post-metadata">

**Author:** ![Jamie\_D](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ea5d25/32.png) [@Jamie\_D](https://www.ruby-forum.com/u/Jamie_D)\
**Post date:** [June 23, 2006, 5:28am UTC](https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207/6 "2006-06-23T05:28:01Z")

</div>

On 6/23/06, Jamie D [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> On 22/06/06, Jamie D [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > 
> 
> view:
> 
> > > available and displays this as a form select. In my controller I have:
> > > 
> > > def edit  
> > > @resource = Resource.find(params[:id])  
> > > @sections = Resource.find\_by\_sql “SELECT DISTINCT  
> > > resources.section FROM resources”

I don’t know if it will do what you want but you could try the :select  
option for find.  
I haven’t used it myself so I don’t know if it will work but I think it  
goes  
something like  
@sections = Resouce.find( :all, :select =\> “DISTINCT section”)

---

<div class="post-metadata">

**Author:** ![Jamie\_D](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ea5d25/32.png) [@Jamie\_D](https://www.ruby-forum.com/u/Jamie_D)\
**Post date:** [June 23, 2006, 5:50am UTC](https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207/7 "2006-06-23T05:50:28Z")

</div>

> def edit  
> @resource = Resource.find(params[:id])  
> @sections = Resource.find\_by\_sql “SELECT DISTINCT  
> resources.section FROM resources”  
> end
> 
> And then in my view I have:
> 
> \<% for option in @sections %\>  
> \<option value="\<%= section.section %\>"  
> \<%= ’ selected’ if option.section == @resource.section %\>  
> \<%= option.section %\>  
>   
> \<% end %\>

These errors are tough to track down, but I think rails is getting  
“confused” with resource names or I should say you are confusing rails.

I havent tested but what about trying this:

def edit  
@theresource = Resource.find(params[:id])  
@theoptions = Resource.find\_by\_sql “SELECT DISTINCT  
resources.section as dsection FROM resources”  
end

\<% for theoption in @theoptions %\>  
\<option value="\<%= theoption.dsection %\>"  
\<%= ’ selected’ if theoptions.dsection == @theresource.section %\>  
\<%= thoption.dsection %\>  
  
\<% end %\>

---

<div class="post-metadata">

**Author:** ![Jamie\_D](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ea5d25/32.png) [@Jamie\_D](https://www.ruby-forum.com/u/Jamie_D)\
**Post date:** [June 23, 2006, 8:22am UTC](https://www.ruby-forum.com/t/find-by-sql-cant-print-results/64207/8 "2006-06-23T08:22:52Z")

</div>

Sorted it with

@sections = Resouce.find( :all, :select =\> “DISTINCT section”)

Thanks