find instead of find_by_sql

rubyonrails-talk
1

Dear all,

I need to apply “find” instead of find_by_sql. Kindly suggest me. Because, params[gm] is not working with find_by_sql. Kindly justify whether my code below is in correct syntax…

find_by_sql(“SELECT column1 FROM table WHERE column2 = ‘params[gm]’ ORDER BY column1”)

2

Dear all,

I need to apply “find” instead of find_by_sql. Kindly suggest me. Because, params[gm] is not working with find_by_sql. Kindly justify whether my code below is in correct syntax…

find_by_sql(“SELECT column1 FROM table WHERE column2 = ‘params[gm]’ ORDER BY column1”)

– With Regards Palani Kannan. K Office: +49-531-2616-226 Mobile: +4917647098166

Well, depending on your version of ActiveRecord (and assuming that your model is called Model)

Rails 2:

Model.find(:all, :select => ‘column1’, :conditions => { :column2 => params[gm] }, :order => ‘column1’)

Rails 3:

Model.select(‘column1’).where(:column2 => params[gm]).order(‘column1’).all

But that will give you anemic and readonly instances of Model because they don’t have their id column.

If you want fully formed Model instances, don’t specify the select part.

If you just want the values of column1, you could do something like:

Model.connection.select_values(Model.send(:sanitize_sql, [“SELECT DISTINCT column1 FROM #{Model.table_name} WHERE column2 = ? ORDER BY column1”, params[gm]))

But that’s obviously a bit ugly.

Perhaps you should ask with REAL names from your application so that the answers can be made more naturally.

-Rob

Rob Biedenharn

Rob@AgileConsultingLLC.com http://AgileConsultingLLC.com/

rab@GaslightSoftware.com http://GaslightSoftware.com/

3

Try this:

find_by_sql("SELECT column1 FROM table WHERE column2 = ? ORDER BY column1",params[:gm])

More here: http://api.rubyonrails.org/classes/ActiveRecord/Base.html#method-c-find_by_sql

Walter

4

Dear Rob,

I got it… Thank you for help…

But, I applied code of Rails 2 in Rails 3, It worked for me. It may lead any problem with Rails 2 code in future?

5

It should work fine until Rails 3.1 (when the older ActiveRecord forms will be deprecated)

-Rob

Rob Biedenharn

Rob@AgileConsultingLLC.com http://AgileConsultingLLC.com/

rab@GaslightSoftware.com http://GaslightSoftware.com/

6

Dear Rob,

Thank you for information. I will change all my Rails2 code to Rails3. It will be better in future.