Getting Into Rails Model Queries

For the longest time I was stubborn against learning “database programming”.  It was the abhorrent syntax that made me so resistant.  But low and behold there came Rails who found it in their heart to make a middle man ActiveRecord in which to slay the evil abhorrent syntax by resolving all good details in more reasonable ways.  This in itself was the most enticing thing that brought me to Rails… even so I was still a “I don’t want to be a DB programmer guy”.  Then on my journey of learning I saw a Rails conference talk on Youtube on this thing called Arel.  Yes now we had a Ruby-esk way to write database expressions.  With this I was most delighted, the dragons of DB can stay in their dungeon where I can feed them heaps of tuna and point and laugh at them… in the most loving way of course.

I’ve actually come to enjoy picking up small bits as I go along on getting more advanced things done.  I’m no database expert, but I can definitely get the job done.  At first I would extract the data into Arrays and work with them that way since I knew Ruby well.  But this is wasteful on system resources.  So now I continue to learn over time the many wonders of ActiveRecord and Arel.

Some Basics

If you don’t understand Models yet in Rails I suggest reviewing the Rails documentation on it: Active Model Basics

The most basic data retrieval done in Rails is with find and where.  Find is an id specific, single record to Object retrieval.  But where allows for multiple queries and a collection of results.  User.find(1) will retrieve a record from the database table users with an id of 1.  From there if you have fields like name and age you could access them as if they were methods on the Object instance you created.

user = User.find(1)
user.name
# => "Frodo Baggins"
user.age
# => 33

With where you can pick the fields you want to retrieve data by.

user = User.where(age: 111, home: "Shire")
user.count
# => 1
user.class
# => ActiveRecord::Relation
user.first.name
# => "Bilbo Baggins"

user = User.where(age: 111).where(home: "Shire")
user.first.name
# => "Bilbo Baggins"

where will return an Array like collection called ActiveRecord::Relation.  You can do many neat things with these collection like scoping them to certain parameters.  where will even let you search for lists of things.

users = User.where(age: [33,111])
users.count
# => 2
users.map(&:names)
# => ["Frodo Baggins", "Bilbo Baggins"]

You can use where.not to find things that are not:

users = User.where.not(kind: "Hobbit")
users.first.name
# => "Gandalf"
users = User.where.not(kind: "Hobbit").where(home: "Shire")
# => #<ActiveRecord::Relation []>

That last result is an empty one.

 About Model Methods

When you define your models in your /app/models directory you will be creating methods within the class which is paired to the ActiveRecord database by its table name.  For each column in the database table you will already have methods by that name provided for your Model.  So you don’t need to write methods for name, age, kind, or home if those are fields in your databases table.

When you write regular methods in the model those methods are all instance methods.  In other words def my_method will be a method available on each retrieved record.  You can call it on Frodo, Bilbo, or Gandalfs individual User instances.

# /app/models/user.rb
class User < ActiveRecord::Base
  def my_method
    puts "My name is #{name} and I'm #{age} years old."
  end
end

And when you call it in the console (rails c)

user = User.where(age: 33).first
user.my_method
# => My name is Frodo Baggins and I'm 33 years old.

it works as expected.  Now when you define methods with def self.my_method then it’s no longer an instance method but a Class method.  If you tried it with the last code you would get an error undefined method ‘name’.  Use of methods defined with self here are one good way of scoping things.

# /app/models/user.rb
class User < ActiveRecord::Base
  def self.shire
    User.where(home: "Shire")
  end
end

Assuming we only had the three previous records mentioned; Frodo, Bilbo, and Gandalf; then if we called the method on the model class User it will scope the results to only those in the Shire, which now is two.

users = User.shire
users.count
# => 2
users.map(&:names)
# => ["Frodo Baggins", "Bilbo Baggins"]

The standard way to write scopes is with the scope method.  So it would be written like so:

# /app/models/user.rb
class User < ActiveRecord::Base
  scope :shire, -> { where(home: "Shire") }
end

This will give you the same results as in the previous example.

A Few Useful Methods

ids will turn your ActiveRecord::Collection into an Array of IDs for all the results you have.  So if the User IDs for the 3 people we have is 1 through 3 we’d get the following:

User.all.ids
# => [1,2,3]
users = User.shire
users.ids
# => [1,2]

Remember how I said where can take a list, this can come in pretty handy: where(user_id: User.shire.ids).  This is most helpful when dealing with relationships between different Objects.  Lets say that the User class has_many :emails and the Email class belongs_to :user. Then we can get the emails for those in the Shire like so:

emails = Email.where(user_id: User.shire.ids)
emails.count
# => 2
emails.map(&:address)
# => ["[email protected]", "[email protected]"]

Now I’ve used map a few times to simplify the results down to just the field we want.  Well there’s a method for that.  It’s called pluck.  pluck(:my_symbol) is for use in place of all.map(&:my_symbol).  So we can replace emails.map(&:address) with emails.pluck(:address).  This is a more efficient method for retrieving specific data.  pluck may also take many fields:

names = User.pluck(:name)
names
# => ["Frodo Baggins", "Bilbo Baggins", "Gandalf"]
id_and_name = User.pluck(:id, :name)
# => [[1, "Frodo Baggins"], [2, "Bilbo Baggins"], [3, "Gandalf"]]

Notice if you give pluck one attribute it gives you a flattened Array result.  But if you give it multiple parameters it will group each collection within their own Array inside the result Array.

A Teaser of Arel

Arel really makes the complicated stuff easy.  There are just a few slight syntax things to learn, but it is easy.  Here’s a few examples:

users = User.where( User.arel_table[:age].eq(33) )
users.first.name
# => "Frodo Baggins"

users = User.where( User.arel_table[:age].eq_any([33,111]) )
users.count
# => 2
users.pluck(:name)
# => ["Frodo Baggins", "Bilbo Baggins"]

users = User.where( User.arel_table[:home].not_eq("Shire") )
users.first.name
# => "Gandalf"

What makes Arel so wonderful is you’re able to join complex database queries into one result.  I plan on writing about that in another post.  For now I highly recommend that you watch this RailsConf talk: RailsConf 2014 -Advanced aRel: When ActiveRecord Just Isn’t Enough and see the Arel examples on their github: https://github.com/rails/arel

Summary

Working with databases can be rather fun; especially when you don’t have to dive into very foreign territory.  The more you know, the more you can do, and that’s exciting!  I’ve pretty much focussed here on basics in querying data.  I haven’t written on writing data, or complex relations between different tables/Objects in the database.  There’s so much to touch on.  Polymorphic records are fun once you understand their purpose and use.  These are all things to come.  You can get most of what you may want done with what’s here and some Array work.  Use the tools as you acquire them, until then, work with what you’ve got.

I never thought I’d see the day where I’d have fun working with *SQL databases.  *shudders*  But this is indeed fun.  Feel free to ask me any questions.  Like I said I’m no database expert, but I’ve picked up a lot, and I’m still learning much along the way!  I hope that this was an enjoyable read for you and that the information here was insightful to you.

Please comment, share, subscribe to my RSS Feed, and follow me on twitter @6ftdan!

God Bless!
-Daniel P. Clark

Image by FromTheNorth via the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 Generic License.