CODEWITHSUNDEEP
sqlruby-on-rails
VincentBZ
VincentBZ

Reputation: 232

Non model SQL query with parameters in Rails

Using rails 4, I am using a custom SQL statement that I don't think I can relate to a Model specifically so I am thinking of using a RAW SQL:

  sql = "select categ_a.processu_id, 
                categ_a.category_id as category_id_a,
                categ_a.group_id as group_id_a,
                categ_b.category_id as category_id_b,
                categ_b.group_id as group_id_b
        from
        (select processu_id, category_id, group_id from categorizations where group_id = #{group_id_a}) 
          categ_a INNER JOIN
        (select processu_id, category_id, group_id from categorizations where group_id = #{group_id_b}) 
          categ_b ON categ_a.processu_id = categ_b.processu_id"

  processus_array = ActiveRecord::Base.connection.execute(sql, {:group_id_a => group_id_a, :group_id_b => group_id_b})

The table "categorisations" has - in this case - two entries for each processu_id one for each of the group_id.

My question really is about how to pass parameters to ActiveRecord::Base.connection.execute so that it will take the two group_ids. The statement above gives me an error.

Upvotes: 0

Views: 6248

Answers (2)

MrTheWalrus
MrTheWalrus

Reputation: 9700

Being mindful of SQL injection, you should probably use one of Rails' provided sanitization methods, rather than normal interpolation.

See the first answer here for a snippet that will let you sanitize and execute arbitrary SQL in an AR model: Ruby on Rails: How to sanitize a string for SQL when not using find?

If you include that, you should be able to do ModelName.execute_sql('select stuff where attribute = ? and other_attribute = ?', first_value, second_value'), just as you would in a normal ActiveRecord .where() method.

Upvotes: 4

Drenmi
Drenmi

Reputation: 8777

You can use Kernel#sprintf to format your string in place. For example, if your IDs are integers:

sql = "SELECT
         categ_a.processu_id, 
         categ_a.category_id as category_id_a,
         categ_a.group_id as group_id_a,
         categ_b.category_id as category_id_b,
         categ_b.group_id as group_id_b
       FROM
         (SELECT processu_id, category_id, group_id FROM categorizations WHERE group_id = %d) categ_a
       INNER JOIN
         (SELECT processu_id, category_id, group_id FROM categorizations WHERE group_id = %d) categ_b
       ON
         categ_a.processu_id = categ_b.processu_id"

processus_array = ActiveRecord::Base.connection.execute(sprintf(sql, group_id_a, group_id_b))

Be careful, though, that you don't create any opportunities for SQL injection.

Upvotes: -3

Related Questions