Rails find_by_sql using ruby variable

Question

I'm trying to select workorders using find_by_sql. The where portion of the sql needs to test against some ruby code:

I tried this:

<% Workorder.find_by_sql("SELECT * FROM workorders w JOIN empgroups e USING (workgroup_id) WHERE e.employee_id = ?, <%= current_user.employee.id %>").each do |workorder| %>

But, it doesn't seem to pre-process the <%= current_user.employee.id %>

Thanks for the help!

Answer 1

There is a little mistake in your syntax. find_by_sql and where expects an array when you are using ? notation for values.
Also, there is no need to interpolate current_user.employee.id
Replace your query with :

<% Workorder.find_by_sql(["SELECT * FROM workorders w JOIN empgroups e USING (workgroup_id) WHERE e.employee_id = ?", current_user.employee.id]).each do |workorder| %>

Answer 2

First of all, there is much better to place your selecting code to the controller side, like this:

def your_controller_method
  @workorders = Workorder.find_by_sql('SELECT * FROM workorders w JOIN empgroups e USING (workgroup_id) WHERE e.employee_id = ?', current_user.employee.id)
end

And then you can use it in the view:

<% @workorders.each do |workorder| %>
<% end %>