postgresql AREL:使用from子句编写复杂的更新语句

yc0p9oo0  于 2023-06-29  发布在  PostgreSQL
关注(0)|答案(2)|浏览(104)

我试着寻找一个使用Arel::UpdateManager来形成一个带有from子句的update语句的例子(就像在UPDATE t SET t.itty = "b" FROM .... WHERE ...中一样),找不到任何例子。在我看来,Arel::UpdateManager在初始化时设置主引擎,并允许设置要更新的各种字段和值。真的有办法做到这一点吗?
另一个例外是找出如何将Postgres posix正则表达式匹配表示为ARel,但这现在可能是不可能的。

6fe3ivhb

6fe3ivhb1#

据我所知,当前版本的arel gem不支持sql查询的 FROM 关键字。您可以仅使用 SETWHERE 关键字生成查询,例如:

UPDATE t SET t.itty = "b" WHERE ...

而将units表的值从field2复制到field1的代码如下:

relation = Unit.all
um = Arel::UpdateManager.new(relation.engine)
um.table(relation.table)
um.ast.wheres = relation.wheres.to_a
um.set(Arel::Nodes::SqlLiteral.new('field1 = "field2"'))
ActiveRecord::Base.connection.execute(um.to_sql)

你可以使用额外的方法来更新一个关系。因此,我们创建Arel的UpdateManager,并为其分配表、where子句和要设置的值。值可以作为参数传递给方法。然后我们需要将FROM关键字添加到生成的SQL请求中,只有当我们可以通过UPDATE子句本身访问指定的外部表时才添加它。最后我们执行查询。所以我们得到:

def update_relation!(relation, values)
   um = Arel::UpdateManager.new(relation.engine)
   um.table(relation.table)
   um.ast.wheres = relation.wheres.to_a
   um.set(values)
   sql = um.to_sql

   # appends FROM field to the query if needed
   m = sql.match(/WHERE/)
   tables = relation.arel.source.to_a.select {|v| v.class == Arel::Table }.map(&:name).uniq
   tables.shift
   sql.insert(m.begin(0), "FROM #{tables.join(",")} ") if m && !tables.empty?

   # executes the query
   ActiveRecord::Base.connection.execute(sql)
 end

您可以发出关系更新为:

values = Arel::Nodes::SqlLiteral.new('field1 = "field2", field2 = NULL')
relation = Unit.not_rejected.where(Unit.arel_table[:field2].not_eq(nil))
update_relation!(relation, values)
i86rm4rw

i86rm4rw2#

基于Мал Скрылев的答案,这是我的想法,它可以让你写得非常类似于现有的relation.update_all(它不支持from):

relation.update_all_from('something = other_table.something', from: 'other_table')

来源如下:

class ActiveRecord::Relation
  # If you try to use update_all to do an SQL update that requires a "from" clause, you will end up getting:
  #   PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "table_name"
  # This variation lets you add the needed "from" clause.
  #
  # Usage:
  #   relation.update_all_from('something = other_table.something', from: 'other_table')
  #
  # This is a cross between update_all from activerecord gem and
  # https://stackoverflow.com/questions/22501829/arel-writing-complex-update-statements-with-from-clause/23967126#23967126
  #
  def update_all_from(updates, from: nil)
    stmt = Arel::UpdateManager.new

    stmt.set Arel.sql(@klass.sanitize_sql_for_assignment(updates))
    stmt.table(table)

    if has_join_values? || offset_value
      @klass.connection.join_to_update(stmt, arel, arel_attribute(primary_key))
    else
      stmt.key = arel_attribute(primary_key)
      stmt.take(arel.limit)
      stmt.order(*arel.orders)
      stmt.wheres = arel.constraints
    end

    # Appends from clause to the query if needed
    sql = stmt.to_sql
    m = sql.match(/WHERE/)
    if from
      from = Array(from)
    else
      from = arel.source.to_a.select {|v| v.class == Arel::Table }.map(&:name).uniq[1..-1]
    end
    if m && !from.empty?
      sql.insert(m.begin(0), "FROM #{from.join(",")} ")
    end

    @klass.connection.update sql, "#{@klass} Update All"
  end
end

相关问题