Find_by_sql, can't print results

Rails
1

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

2

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

3

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

4

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

5

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]

6

On 6/23/06, Jamie D [email protected] wrote:

On 22/06/06, Jamie D [email protected] 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”)

7

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 %>

8

Sorted it with

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

Thanks