Sometimes, usually for performance reasons, it might be necessary to do raw SQL statements. Most of the time save_without_transactions is all you might need. But, if you still want to explicitly call an insert or an update for whatever reason, you're able to use the model connection's execute function. However, using (ActiveRecord)AR I've gotten quite lazy with writing queries. Here's something quick dirty I whipped up to generate queries.
class ActiveRecord::Base
def return_value_string(value)
case value.class.to_s
when "Time": "'#{value.strftime("%Y-%m-%d %H:%M:%S")}'"
when "NilClass": "NULL"
when "Fixnum": value
when "String": "'#{value.escape_single_quotes}'"
when "FalseClass": '0'
when "TrueClass": '1'
else "'#{value}'"
end
end
def generate_update_query
"UPDATE #{self.class.table_name} SET " +
self.attributes.keys.sort.collect{ |key|
"`#{key}` = #{return_value_string(self.send(key))}" }.join(", ") +
" WHERE id = #{self.id}"
end
def generate_insert_query
@key_vals = self.attributes.collect{ |key,value|
[key, return_value_string(value)] }
"INSERT INTO #{self.class.table_name} " +
"( #{@key_vals.collect{ |item| item[0].to_s }.join(", ") } ) " +
"VALUES( #{@key_vals.collect{ |item| item[1].to_s }.join(", ") } ) "
end
def raw_update
self.class.connection.execute(self.generate_update_query)
end
def raw_insert
self.class.connection.execute(self.generate_insert_query)
end
end
Now I can just do object.raw_insert or object.raw_update. I tried cover most of the data types I can think of in the 'return_value_string' function (I'm working with MySQL), but let me know of anything I might have missed. Another thing to play with is connection.insert and connection.update. I believe connection.insert can return the id of the row you just created so that might some slight bit of overhead that can be avoided. Doing queries this way might be better suited for data migrations rather than normal application requests. There's still the option of tracing through AR and see how it generates queries but this works well enough for now. As always, feedback is welcome.
UPDATE:
here is the revamped version for raw_insert and raw_update ripped directly from activerecord source. now you can get the id back on your inserts too. yay!
def raw_update
quoted_attributes = attributes_with_quotes(false)
return 0 if quoted_attributes.empty?
connection.update(
"UPDATE #{self.class.table_name} " +
"SET #{quoted_comma_pair_list(connection, quoted_attributes)} " +
"WHERE #{connection.quote_column_name(self.class.primary_key)} = #{quote_value(id)}",
"#{self.class.name} Update"
)
end
def raw_insert
if self.id.nil? && self.class.connection.prefetch_primary_key?(self.class.table_name)
self.id = self.class.connection.next_sequence_value(self.class.sequence_name)
end
quoted_attributes = attributes_with_quotes
statement = if quoted_attributes.empty?
self.class.connection.empty_insert_statement(self.class.table_name)
else
"INSERT INTO #{self.class.table_name} " +
"(#{quoted_column_names.join(', ')}) " +
"VALUES(#{quoted_attributes.values.join(', ')})"
end
self.id = self.class.connection.insert(statement, "#{self.class.name} Create",
self.class.primary_key, self.id, self.class.sequence_name)
@new_record = false
id
end
