Raw DB querying in Rails

Question

I'm trying to run the following raw query in rails, only to see it fail:

query   = 'SELECT * FROM users WHERE id IN ($1);'
results = ActiveRecord::Base.connection.exec_query(query, "My query", [ [1,2] ]);

What am I doing wrong?

The error I'm getting starts with this:

Could not log "sql.active_record" event. NoMethodError: undefined method `binary?' for 1:Fixnum

Clearly, I'm misusing [1, 2] bind params somehow, but I couldn't find a proper example myself.

P.S. This is minimal failing example, derived of a much more advanced query that can't be turned into ActiveRecord chain of calls. In other words – I can't rely on Arel when building my query.

P.P.S. I'm using rails 4.0.1 and postgresql 9.3

Tenorio

I am not sure about postgresql since I usually query for MySQL. But anyways, the syntax should be same. Try: query = 'SELECT * FROM users WHERE id IN (?);' results = ActiveRecord::Base.connection.exec_query(query, [1,2]) – Etruria

Answer 1

I'm pretty sure your NoMethodError is coming from the logging stuff. If we look exec_query, we see this:

def exec_query(sql, name = 'SQL', binds = [])
  log(sql, name, binds) do
    # call exec_no_cache(sql, binds) or exec_cache(sql, binds)...

Then if we look at exec_cache, we see this:

def exec_cache(sql, binds)
  #..
  @connection.send_query_prepared(stmt_key, binds.map { |col, val|
    type_cast(val, col)
  })

so the binds are supposed to be column/value pairs. The PostgreSQL driver expects col to be a column object so that it can ask it what its name is and how it should format the val, that information is used by the log call in exec_query to produce something pretty and human-readable in the Rails logs. A bit of experimentation suggests that you can use nil as the col and everything is happy.

That means that we've moved on to this:

exec_query(
  'SELECT * FROM users WHERE id IN ($1)',
  'my query',
  [ [nil, [1,2]] ]
)

The underlying driver may or may not know what to do with the [1,2] array, I only have Rails3 with the PostgreSQL extensions available to test and it doesn't like the [1,2]. If Rails4 also doesn't like the array then you can pass the arguments one-by-one:

exec_query(
  'SELECT * FROM users WHERE id IN ($1, $2)',
  'my query',
  [ [nil,1], [nil,2] ]
)

Superstar

Answer 2

I ran into a similar issue lately. It turns out that in the where in (?), ActiveRecord is expecting a string, not an array. So you can try passing in a string of comma-separated ids and that should do the trick.

Tinnitus

I ran into this issue too but solved it by doing this: sql = "SELECT * FROM table_a WHERE identifier IN (?)" where_equals = ["id_1", "id_2"] ActiveRecord::Base.connection.exec_query(ActiveRecord::Base.send(:sanitize_sql_array, [sql, where_equals])) – Supererogate

the (?) syntax did not work for me at first. I switched to using the alternate '%s' syntax and then eventually hash syntax. e.g. sanitize_sql_array(["name=:name and group_id=:group_id", name: "foo'bar", group_id: 4]) # => "name='foo''bar' and group_id=4" sanitize_sql_array(["name='%s' and group_id='%s'", "foo'bar", 4]) # => "name='foo''bar' and group_id='4'" from api.rubyonrails.org/classes/ActiveRecord/Sanitization/… – Silvers