NOOB: Using locals to pass data to a SQL query


#1

I want to show a users entries based on there login id. I have tried
this:

View:

<%= render :partial => 'myfish', :locals => {:user => session [:user].id } %>

Partial:

<% for entry in @entries %>

<% end %>

Model:

def self.mine
find_by_sql(“select entries.id, species.name,
length,species.multiplier*length as score,caught_on from entries join
species on species_id = species.id where user_id = ? order by score
DESC”, myfish)
end

Basically I am trying to get the current value of session[:user].id
into the find_by_sql query. Any pointers?

  • Bill
<%=h entry.name %> - <%=h entry.length %> Inches - <%=h entry.score %> Points - <%=h entry.caught_on.strftime("%m-%d-%Y") %> <%= link_to 'Show', :action => 'show', :id => entry %> <%= link_to 'Delete', { :action => 'destroy', :id => entry }, :confirm => 'Are you sure?' %>

#2

On Jan 20, 2006, at 8:47 , Bill P. wrote:

def self.mine
find_by_sql(“select entries.id, species.name,
length,species.multiplier*length as score,caught_on from entries
join species on species_id = species.id where user_id = ? order by
score DESC”, myfish)
end

I’m assuming the error is in your find_by_sql call, though since you
haven’t described what the error is, I’m not sure. I think the main
problems is that you may need to pass an array literal to find_by_sql
(using the square brackets) to interpolate (and properly escape) the
value of myfish. Another thing I did was use a here document to make
it a little easier to read (and edit) the sql query itself: the query
is stored in the sql variable. This isn’t necessary, but I think it
makes for more readable code.

def self.mine
sql = <<-_SQL
select entries.id
, species.name
, length
, species.multiplier*length as score
, caught_on
from entries
join species on species_id = species.id
where user_id = ?
order by score DESC
_SQL
find_by_sql([sql, myfish])
end

Hope this helps! If it doesn’t, perhaps supply a bit more information
about the trouble you’re having and what errors your getting so
someone can get a better idea of what is going on.

Michael G.
grzm myrealbox com


#3

duh sorry I did not post the error. With my code and your code I get
this:

NameError in Entries#list

undefined local variable or method `myfish’ for Entry:Class

On Jan 19, 2006, at 7:39 PM, Michael G. wrote:

you haven’t described what the error is, I’m not sure. I think the
, species.name

http://lists.rubyonrails.org/mailman/listinfo/rails

  • Bill