Rails - Jan 2006 - NOOB: Using locals to pass data to a SQL query

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

View:

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

Partial:

<% for entry in @entries %>
<table border ="0" CELLSPACING="0">
<tr class="<%= alternate %>">

   <td><%=h entry.name %> - </td>
   <td><%=h entry.length %> Inches - </td>
   <td><%=h entry.score %> Points - </td>
   <td><%=h entry.caught_on.strftime("%m-%d-%Y")
%></td>
   <td><%= link_to ''Show'', :action =>
''show'', :id => entry %></td>
   <td><%= link_to ''Delete'', { :action =>
''destroy'', :id =>
entry }, :confirm => ''Are you sure?'' %></td>
   </tr>
<% 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

On Jan 20, 2006, at 8:47 , Bill Pennington 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 Glaesemann
grzm myrealbox com

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 Glaesemann wrote:
>
> On Jan 20, 2006, at 8:47 , Bill Pennington 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 Glaesemann
> grzm myrealbox com
>
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>


- Bill