Saturday, June 12, 2010

A Few Simple Ruby, Rails, and MySQL Tips

Sometimes it makes sense to store the value of a query in a variable within MySQL. You might do this if you're avoiding ActiveRecord because you're in a data migration. Here's how:
    execute %{SELECT (@setuid_id:=id) FROM roles WHERE title = 'setuid'};
execute %{SELECT (@admin_id:=id) FROM roles WHERE title = 'admin'};
Similarly, sometimes it's helpful to use the results of a query in order to insert several rows at the same time. MySQL has INSERT...SELECT syntax that can be used like this:
    execute %{
INSERT INTO roles_users (role_id, user_id)
SELECT @setuid_id, user_id
FROM roles_users
WHERE role_id = @admin_id
The above query finds all the admins and gives them the setuid role as well.

If you're a Python coder coding in Ruby, don't forget that ["a", "b", "c"] is more simply written %w(a b c).

You probably know that you can lookup rows via ActiveRecord finder methods like Role.find_by_name(name). However, did you know that you can also find multiple records at the same time via Role.find_all_by_name(names_array)?

Remember that Role.find_by_name(name) will return nil if there is no such record, whereas Role.find_by_name!(name) will raise an exception if there is no such record. Unfortunately, there is no "!" version of Role.find_all_by_name. Hence, if you need to make sure that every name resulted in a record, you'll have to look at the number of records returned.

No comments: