How to pass array as bind variable to Rails/ActiveRecord raw SQL queries?

Question

I need to pass an array of ids into my raw sql query like this:

select offers.* from offers where id in (1,2,3,4,5)

The real query includes a lot of joins and aggregation functions and can't be written using Arel expressions or ActiveRecord model methods like Offer.where(id: [...]). I'm looking exactly for how to use bind variables in raw queries.

Instead of interpolating ids into string I want to use bind variables like this (pseudo-code):

ActiveRecord::Base.connection.select_all("select offers.* from offers where id in (:ids)", {ids: [1,2,3,4,5]})

However, I can't find any solution to perform this. From this ticket I've got a comment with related test-case in ActiveRecord code with the following example:

sub   = Arel::Nodes::BindParam.new
binds = [Relation::QueryAttribute.new("id", 1, Type::Value.new)]
sql   = "select * from topics where id = #{sub.to_sql}"

@connection.exec_query(sql, "SQL", binds)

I've tried this approach, but it didn't worked at all, my "?" was not replaced by actual values.

I'm using Rails 5.1.6 and MariaDB database.

Vova Rozhkov · Accepted Answer · 2019-02-05 20:50:08Z

0

You could do this in a much simpler fashion purely with arel. (Also it makes the code far more maintainable than SQL strings)

offers = Arel::Table.new('offers') 
ids = [1,2,3,4,5]
query = offers.project(Arel.star).where(offers[:id].in(ids))
ActiveRecord::Base.connection.exec_query(query.to_sql)

This will result in the following SQL

SELECT 
  [offers].*
FROM 
  [offers]
WHERE 
  [offers].[id] IN (1,2,3,4,5)

When executed you will receive an ActiveRecord::Result object with is usually easiest to deal with by calling to_hash and each resulting row will be turned into a Hash of {column_name => value}

However if you are using rails and Offer is a true model then:

Offer.where(id: ids)

Will result in the same query and will return an ActiveRecord::Relation collection of Offer objects which is generally more preferable.

Update

Seems like you need to enable prepared_statements in mysql2 (mariadb) in order to use the bind params, which can be done like this:

default: &default
  adapter: mysql2
  encoding: utf8
  prepared_statements: true  # <- here we go!

Please note the following pieces of code: https://github.com/rails/rails/blob/5-1-stable/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb#L115

https://github.com/rails/rails/blob/5-1-stable/activerecord/lib/active_record/connection_adapters/mysql2_adapter.rb#L40

https://github.com/rails/rails/blob/5-1-stable/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb#L630

https://github.com/rails/rails/blob/5-1-stable/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb#L30

As you can see in the last code exec_query ignores bind_params if prepared_statements is turned off (which appears to be the default for the mysql2 adapter).

edited Feb 5, 2019 at 20:50

Vova Rozhkov

1,7522 gold badges20 silver badges29 bronze badges

answered Feb 4, 2019 at 14:51

engineersmnky

30.5k2 gold badges42 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Vova Rozhkov Over a year ago

I've used single table for simplicity. The real query is much complex and includes a couple of joins and a log of aggregation functions. I can't use arel or model query methods here.

engineersmnky Over a year ago

@VladimirRozhkov if you can write a valid query you can use arel to build it. I have arel built queries that translate into 500 lines of SQL using tens of CTEs and joins. arel can build anything SQL can handle. If you explain the situation more then I could try and assist

Vova Rozhkov Over a year ago

I'm not looking for solution to my specific case. I'm looking for how to use bind variables in raw queries. This task can be easily done in other ORM's, why ActiveRecord should be different?

Vova Rozhkov Over a year ago

It works with single value but didn't work with arrays.

kevlarr Over a year ago

"You could do this in a much simpler fashion purely with arel" ... except that query builders are often a lot more obtuse than the corresponding SQL, like in the OP's actual described use case or even something like stackoverflow.com/a/30924648/4747937 . Being able to build any SQL query makes arel powerful, but it doesn't mean it's simpler.

|

Collectives™ on Stack Overflow

How to pass array as bind variable to Rails/ActiveRecord raw SQL queries?

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related