Raw DB querying in Rails
Solution 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] ]
)
Solution 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.
Related videos on Youtube
gmile
Updated on June 04, 2022Comments
-
gmile almost 2 years
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
andpostgresql 9.3
-
Utsav KesharwaniI 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])
-
-
saywhatnow about 8 yearsI 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]))
-
Stefan Lyew over 5 yearsthe
(?)
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/…