Rails - How to reference model's own column value during update statement?

user_4746670 0
Viewed 50

Is it possible to achieve something like this?

  • Suppose name and plural_name are fields of Animal's table.
  • Suppose pluralise_animal is a helper function which takes a string and returns its plural literal.
  • I cannot loop over the animal records for technical reasons.
  • This is just an example
Animal.update_all("plural_name = ?", pluralise_animal("I WANT THE ANIMAL NAME HERE, the `name` column's value"))

I want something similar to how you can use functions in MySQL while modifying column values. Is this out-of-scope or possible?

UPDATE animals SET plural_name = CONCAT(name, 's') -- just an example to explain what I mean by referencing a column. I'm aware of the problems in this example.

Thanks in advance

ruby-on-rails ruby mysql
2 Answers

I cannot loop over the animal records for technical reasons.

Sorry, this cannot be done with this restriction.

If your pluralizing helper function is implemented in the client, then you have to fetch data values back to the client, pluralize them, and then post them back to the database.

If you want the UPDATE to run against a set of rows without fetching data values back to the client, then you must implement the pluralization logic in an SQL expression, or a stored function or something.

UPDATE statements run in the database engine. They cannot call functions in the client.

Bill_Karwin_208600

Use a ruby script to generate a SQL script that INSERTS the plural values into a temp table

File.open(filename, 'w') do |file|
  file.puts "CREATE TEMPORARY TABLE pluralised_animals(id INT, plural varchar(50));"
  file.puts "INSERT INTO pluralised_animals(id, plural) VALUES"
  Animal.each.do |animal|
    file.puts( "( #{animal.id}, #{pluralise_animal(animal.name)}),"
  end
end

Note: replace the trailing comma(,) with a semicolon (;)

Then run the generated SQL script in the database to populate the temp table.

Finally run a SQL update statement in the database that joins the temp table to the main table...

UPDATE animals a
INNER JOIN pluralised_animals pa 
  ON a.id = pa.id
SET a.plural_name = pa.plural;
dbugger_157540
Related