Using Rails Update to Append to a Text Column in P

问题:

Thanks in advance for any help on this one.

I have a model in rails that includes a postgresql text column.

I want to append (i.e. mycolumn = mycolumn || newdata) data to the existing column. The sql I want to generate would look like:

update MyOjbs set mycolumn = mycolumn || newdata where id = 12;

I would rather not select the data, update the attribute and then write the new data back to the database. The text column could grow relatively large and I'd rather not read that data if I don't need to.

I DO NOT want to do this:

@myinstvar = MyObj.select(:mycolumn).find(12)
newdata = @myinstvar.mycolumn.to_s + newdata
@myinstvar.update_attribute(:mycolumn, newdata)

Do I need to do a raw sql transaction to accomplish this?

回答1:

I think you could solve this problem directly writing your query using the arel gem, that's already provided with rails.

Given that you have these values:

column_id = 12
newdata = "a custom string"

you can update the table this way:

# Initialize the Table and UpdateManager objects
table = MyOjbs.arel_table
update_manager = Arel::UpdateManager.new Arel::Table.engine
update_manager.table(table)

# Compose the concat() function
concat = Arel::Nodes::NamedFunction.new 'concat', [table[:mycolumn], new_data]
concat_sql = Arel::Nodes::SqlLiteral.new concat.to_sql

# Set up the update manager
update_manager.set(
  [[table[:mycolumn], concat_sql]]
).where(
  table[:id].eq(column_id)
)

# Execute the update
ActiveRecord::Base.connection.execute update_manager.to_sql

This will generate a SQL string like this one:

UPDATE "MyObjs" SET "mycolumn" = concat("MyObjs"."mycolumn", 'a custom string') WHERE "MyObjs"."id" = 12"