find_by_sql snippets

Raw SQL queries with ActiveRecord's find_by_sql and select_all

Tagged activerecord, find_by_sql, parameters, raw, select_all, sql  Languages ruby

Raw SQL queries can be executed with ActiveRecord’s find_by_sql and select_all methods.

If you have an ActiveRecord::Model use find_by_sql:

sql = "select * from honey where bee = :bee offset :offset limit :limit"
honey = Honey.find_by_sql([ sql, bee: 'Russian honey bee', offset: 1000, limit: 10 ])

In this example find_by_sql will return instances of the Honey model. The bind variables can be given as parameters to the find_by_sql method.

The other option is to return the raw SQL results as an array of hashes using select_all:

bee = ActiveRecord::Base.connection.quote('Russian honey bee')
offset = ActiveRecord::Base.connection.quote(1000)
limit = ActiveRecord::Base.connection.quote(10)
sql = "select * from honey where bee = #{bee} offset #{offset} limit #{limit}"

select_all does not accept bind variable, so you need to insert any SQL conditions into the SQL and quote them yourself.