Rails - Dec 2008 - Problems with getting correct id from query involving two tables

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

you add...

:select => "transfers.*"

On Dec 9, 8:36 am, Ken
<kenneth.m.mcdon...-rphTv4pjVZMJGwgDXS7ZQA@public.gmane.org>
wrote:
> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

Thanks,
Ken

On Dec 9, 2008, at 10:41 AM, Andy Koch wrote:
>
> you add...
>
> :select => "transfers.*"
>
> On Dec 9, 8:36 am, Ken
<kenneth.m.mcdon...-rphTv4pjVZMJGwgDXS7ZQA@public.gmane.org> wrote:
>> 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
> >

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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)



On Tue, Dec 9, 2008 at 1:36 PM, Ken
<kenneth.m.mcdonald-rphTv4pjVZMJGwgDXS7ZQA@public.gmane.org>
wrote:
>
> 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
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---