我可以执行原始sql查询，利用准备好的语句而不使用ActiveRecord :: Relation :: QueryAttribute吗？ [英] Can I execute a raw sql query, leverage prepared statements, and not use ActiveRecord::Relation::QueryAttribute?

问题描述

我想做一个 upsert 。 Rails尚不支持此功能。查询如下所示：

I want to do an upsert. Rails doesn't support this yet. The query is something like this:

INSERT INTO foos (thing_id, bar_id) VALUES (1, 2)
ON CONFLICT (thing_id, bar_id) DO NOTHING

我可以轻松地做到这一点 self.class.connection.execute 或 exec_insert 。但我也想利用准备好的陈述。我以为可以这样做：

I can easily do this with self.class.connection.execute or exec_insert. But I want to also leverage prepared statements. I thought I can do this like so:

thing_id = ActiveRecord::Relation::QueryAttribute.new("thing_id", thing.id, ActiveRecord::Type::Integer.new)
bar_id = ActiveRecord::Relation::QueryAttribute.new("bar_id", id, ActiveRecord::Type::Integer.new)

self.class.connection.exec_insert(<<-SQL, nil, [thing_id, bar_id])
  INSERT INTO foos (thing_id, bar_id) VALUES ($1, $2)
  ON CONFLICT (thing_id, bar_id) DO NOTHING
SQL

我尝试过这种做法，似乎未创建准备好的语句。

But when I experimented with this it seems that a prepared statement is not created.

我尝试了以下样式：

query = <<-SQL
  INSERT INTO foos (thing_id, bar_id) VALUES ($1, $2)
  ON CONFLICT (thing_id, bar_id) DO NOTHING
SQL

connection = ActiveRecord::Base.connection.raw_connection
connection.prepare('some_name', query)
st = connection.exec_prepared('some_name', [ thing.id, id ])

它确实创建了一个准备好的声明。但是，第二次运行时，postgres抱怨创建一个具有相同名称的准备好的语句。因此，Rails的预备语句管理在此之上进行，而我在这里无法利用它。我将不得不在这里手动进行管理。我不敢确定我是否可以正确执行此操作，即使我这样做也会很冗长。

And it does create a prepared statement. BUT, the second time it is run, postgres complains about creating a prepared statement with the same name. So, rails' prepared statement management happens at a level above this, and I'm unable to leverage it here. I would have to manually manage it here. I'm not confident I could do this properly, and even if I could it would be verbose.

执行和朋友不接受 where 接受的（ foo =？，1） api。

execute and friends do not accept the ("foo=?", 1) api that where accepts.

有什么方法可以利用Rails的自动预准备语句管理原始SQL吗？

Is there any way to leverage rails' automagic prepared statement management for raw SQL?

推荐答案

#exec_query 接受可选的关键字参数 prepare ，默认为false。看看方法定义（和这里）。

#exec_query accepts an optional keyword argument, prepare, which defaults to false. Take a look at the method definition (and here).

基于下表的定义：

CREATE TABLE foos (
    thing_id INT,
    bar_id INT,
    UNIQUE (thing_id, bar_id)
);

我测试了以下内容：

conn = ActiveRecord::Base.connection
stmt = 'INSERT INTO foos (thing_id, bar_id) VALUES ($1, $2) ' \
       'ON CONFLICT (thing_id, bar_id) DO NOTHING'

# "Old" bind parameters
binds = [[nil, 1], [nil, 2]]
conn.exec_query stmt, 'SQL', binds, prepare: true
conn.exec_query stmt, 'SQL', binds, prepare: true

# "New" bind parameters
type  = ActiveModel::Type::Integer.new limit: 4
binds = [
  ActiveRecord::Relation::QueryAttribute.new('thing_id', 1, type),
  ActiveRecord::Relation::QueryAttribute.new('bar_id',   2, type)
]
conn.exec_query stmt, 'SQL', binds, prepare: true
conn.exec_query stmt, 'SQL', binds, prepare: true

这两种绑定参数样式均适用于ActiveRecord 5.2.0和第1.0.0页。多个使用相同值的INSERT语句最终将仅插入一行，而不会引发任何错误。我检查了Postgres日志，并且只有一个解析（在第一个INSERT之前），因此看来准备好的语句机制正在正确使用。

Both of those bind param styles worked for me with ActiveRecord 5.2.0 and pg 1.0.0. Multiple INSERT statements using the same values ultimately result in only one row being inserted with no errors raised. I checked the Postgres logs, and there was only one "parse" (before the first INSERT), so it appears that the prepared statement mechanism is being used correctly.

这篇关于我可以执行原始sql查询，利用准备好的语句而不使用ActiveRecord :: Relation :: QueryAttribute吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！