Matthew Cliatt Matthew Cliatt - 7 months ago 20
SQL Question

Arguments to update_attribute change in SQL statement

The gist of it all



This method call:

user.update_attribute('image_url', 'arch.png')


Generates this SQL statement:

UPDATE "users" SET "image_url" = ?, [["image_url", "rock.jpg"]]


That SQL statement is not what the desired outcome is.

The desired outcome is this SQL statement:

UPDATE "users" SET "image_url" = ?, [["image_url", "arch.png"]]


Notice that the SQL statement generated has the wrong argument for
image_url
.


  • How is that possible though?

  • How can I fix it?






Background



I use the
form_for
tag to generate a form where users can change their profile picture. The form sends a post request to the controller which calls another method, passing along the parameters. The final method calls
update_attribute
on the record, saving the changes.

I'm using the gem "carrierwave" for the images.




Debugging



I've done some debugging and it looks like everything goes well, until the last method calls
update_attribute
. That call generates a SQL statement which is incorrect.

For reference purposes, the old picture's file name is "rock.jpg", and the new one is "arch.png".

The gist of it is that these two lines are executed:

puts "\n\nSending #{attribute} and #{value} to update_attribute\n\n"
if user.update_attribute(attribute, value)


Which result in:

Sending image_url and arch.png to update_attribute

UPDATE "users" SET "image_url" = ?, WHERE "users"."id" = ? [["image_url", "rock.jpg"], [...]]


I took out the updated_at part of the generated SQL statement, since it isn't important. The full SQL statement as well as more output from the helper can be seen just below.


  • How is the
    image_url
    in the SQL statement different from the value passed to it?



Detailed output from log



Received update request!!!!!
---------------------------
Attribute - image_url
Value - arch.png
--------------------------

Sending image_url and arch.png to update_attribute

(0.1ms) begin transaction
CACHE (0.0ms) SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT 1 [["id", 186]]
SQL (0.2ms) UPDATE "users" SET "image_url" = ?, "updated_at" = ? WHERE "users"."id" = ? [["image_url", "rock.jpg"], ["updated_at", "2016-04-27 01:25:49.476024"], ["id", 186]]
(90.4ms) commit transaction





Code



Form:

<%= form_for(@current_member, html: {id: 'image_form'}) do %>
<%= file_field_tag :image_url,
type: 'file', html: {id: 'file_field'} %>
<%= hidden_field_tag 'updateParam', 'image_url' %>
<%= submit_tag %>
<% end %>


Controller:

def update
update_account(@current_member, params[:updateParam], params)
end


Helper:

def update_account(user, attribute, parameters)

puts "\n\n\n\n\n"
puts "Received update request!!!!!\n
puts "---------------------------\n"
puts "Attribute - #{attribute}\n"
puts "Value - #{parameters[attribute]}\n"
puts "--------------------------\n\n\n"

if editable attribute
if valid(attribute, parameters)
value = parameters[attribute]

puts "\n\nSending #{attribute} and #{value} to update_attribute\n\n"

if user.update_attribute(attribute, value)

# ...
# ...





The gist of it all



This method call:

user.update_attribute('image_url', 'arch.png')


Generates this SQL statement:

UPDATE "users" SET "image_url" = ?, [["image_url", "rock.jpg"]]


That SQL statement is not what the desired outcome is.

The desired outcome is this SQL statement:

UPDATE "users" SET "image_url" = ?, [["image_url", "arch.png"]]


Notice that the SQL statement generated has the wrong argument for
image_url
.


  • How is that possible?

  • How can I fix it?


dkp dkp
Answer

Extending comments to answer:

It looks, there is some callback in your model which is changing the value before save/update.

To skip callbacks, you can use update_column instead of update_attribute.

Note: update_column also skips validations and does not update the updated_at attribute.


Edit from author

This answer was right, a callback changed the result to something that could be saved in the database.

Using update_column I was able to see that my parameters were passed in correctly, but were not correct. The carrierwave gem uses a callback which checks to see if the parameter is valid, if not, it reverts the value to what it was previous to the call. If the value is valid (an UploadedFile), then it saves the file in the save_dir and changes the value to be a url to point to the file.

I used this to debug further and find that the value I passed to update_attribute wasn't valid because the form_for didn't include multipart: true, which is necessary for forms using carrierwave to upload images. Adding this solved the problem.

I had to go back to using update_attribute instead of update_column so that carrierwave's callback would still be executed and the uploaded file would be saved in the database.

Comments