Passing a variable into SQL statement with rails

Question

I'm trying to pass the variable supplier into the where clause of my sql statement. It saves the variable as when i click the link it shows the chosen supplier in the url. I just have no idea what to put in the statement to make it pick up the variable from the view.

Model:

def self.find_supplier
find_by_sql(["SELECT s.supplier, s.productcode, s.description, CAST(SUM(l.amount) as UNSIGNED) AS amount
FROM softwares s
LEFT JOIN licenses l ON s.id=l.software_id
WHERE s.supplier = ? AND l.amount > 0
GROUP BY s.supplier, s.vendor, s.title, s.edition", :supplier)
end

View:

<% @softwares.each do |l| %>
<li>
<%= link_to "#{l.supplier}", :controller =>'softwares', :action => 'export_supplier', :supplier => l.supplier %>
</li>
<% end %>

Controller:

def export_supplier
@software = Software.find_supplier
software = CSV.generate do |csv|

# for the headers of the csv file
  csv << ["Quantity", "Item Number", "Item Description"]
  # the chosen rows from the database
  @software.each do |s|
    csv << [s.amount, s.productcode, s.description, s.supplier]
  end
end
send_data(software, :type => 'text/csv', :filename => 'software.csv')
end

Ross · Accepted Answer · 2012-10-11 12:13:04Z

1

find_by_sql(["SELECT s.supplier, s.productcode, s.description, CAST(SUM(l.amount) as UNSIGNED) AS amount

FROM softwares s LEFT JOIN licenses l ON s.id=l.software_id WHERE s.supplier = ? AND l.amount > 0 GROUP BY s.supplier, s.vendor, s.title, s.edition", params[:supplier])

I assume the find_by_sql is in your controllers, "export_supplier" method. Here you should get params[:supplier]

answered Oct 11, 2012 at 12:13

Ross

1,5721 gold badge15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1738017 Over a year ago

I've updated my question with the code in my controller, I've added what you said and i get this error undefined local variable or method 'params'

Ross Over a year ago

The params are not accessible in the model, you have to pass it to the model as a paramter Software.find_supplier(params[:supplier]) and in the model def self.find_supplier(supplier) ....... end

Ross Over a year ago

then you will have a supplier variavle in model, and not :supplier I would suggest go through some good rails book first like the rails 3 way by Obie

Collectives™ on Stack Overflow

Passing a variable into SQL statement with rails

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related