postgresql Rails 中的原始数据库查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21413381/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Raw DB querying in Rails
提问by gmile
I'm trying to run the following raw query in rails, only to see it fail:
我正在尝试在 Rails 中运行以下原始查询，却发现它失败了：
query = 'SELECT * FROM users WHERE id IN ();'
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.
显然，我[1, 2]以某种方式滥用了绑定参数，但我自己找不到合适的例子。
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.
PS 这是最小的失败示例，源自无法转换为 ActiveRecord 调用链的更高级的查询。换句话说 – 在构建查询时我不能依赖 Arel。
P.P.S. I'm using rails 4.0.1and postgresql 9.3
我正在使用的 PPSrails 4.0.1和postgresql 9.3
采纳答案by mu is too short
I'm pretty sure your NoMethodError is coming from the logging stuff. If we look exec_query, we see this:
我很确定您的 NoMethodError 来自日志记录。如果我们看exec_query，我们会看到：
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:
然后，如果我们查看exec_cache，我们会看到：
def exec_cache(sql, binds)
#..
@connection.send_query_prepared(stmt_key, binds.map { |col, val|
type_cast(val, col)
})
so the bindsare supposed to be column/value pairs. The PostgreSQL driver expects colto 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 logcall in exec_queryto produce something pretty and human-readable in the Rails logs. A bit of experimentation suggests that you can use nilas the coland everything is happy.
所以binds应该是列/值对。PostgreSQL 驱动程序期望col是一个列对象，以便它可以询问它的名称是什么以及它应该如何格式化val，log调用使用该信息exec_query在 Rails 日志中生成一些漂亮且可读的内容。一些实验表明您可以使用nilascol并且一切都很愉快。
That means that we've moved on to this:
这意味着我们已经转向了这个：
exec_query(
'SELECT * FROM users WHERE id IN ()',
'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:
底层驱动程序可能知道也可能不知道如何处理[1,2]数组，我只有带有 PostgreSQL 扩展的 Rails3 可供测试，它不喜欢[1,2]. 如果 Rails4 也不喜欢这个数组，那么你可以一一传递参数：
exec_query(
'SELECT * FROM users WHERE id IN (, )',
'my query',
[ [nil,1], [nil,2] ]
)
回答by Edmond Chui
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.
我最近遇到了类似的问题。事实证明，在 中where in (?)，ActiveRecord 需要一个字符串，而不是一个数组。因此，您可以尝试传入一串以逗号分隔的 ID，这应该可以解决问题。
