Problems with getting correct id from query involving two tables


#1

I have a query that is intended to find all “transfers” based on a
condition the uses a second table. In the controller, it looks like
this:

def find_protocols
@transfers = Transfer.find(:all, :from => “transfers,
protocols”, :conditions => “transfers.protocol_id = protocols.id AND
protocols.name = “#{params[:protocol]}””)
respond_to do |format|
format.html # index.html.erb
format.xml { render :xml => @transfers }
end
end

It works great, with one problem; the id’s associated with the
rendered objects are “transfers.protocol_id” and not
“transfers.id” (or at any rate, they are certainly not
“transfers.id”). Anyone have recommendations to fix this? I’d prefer
something that allows me to stay at the SQL level, because I am quite
comfortable working with the SQL queries.

Many thanks,
Ken


#2

you add…

:select => “transfers.*”


#3

Thanks. I also found that reversing the order of the tables worked,
but the select will be a lot more reliable.

Thanks,
Ken


#4

You should NEVER do this:

@transfers = Transfer.find(:all, :from => “transfers, protocols”,
:conditions => “transfers.protocol_id = protocols.id AND
protocols.name = “#{params[:protocol]}””)

You’re opening up your site for SQL injection attacks, do it using
placeholder variables:

@transfers = Transfer.find(:all, :from => “transfers, protocols”,
:conditions => [“transfers.protocol_id = protocols.id AND
protocols.name = ?”, params[:protocol] ])

Maurício Linhares
http://alinhavado.wordpress.com/ (pt-br) | http://blog.codevader.com/
(en)