February 6, 2015 by Daniel P. Clark

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)
# => "Frodo Baggins"
# => 33

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

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

user = User.where(age: 111).where(home: "Shire")
# => "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])
# => 2
# => ["Frodo Baggins", "Bilbo Baggins"]

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

users = User.where.not(kind: "Hobbit")
# => "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."

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

user = User.where(age: 33).first
# => 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")

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
# => 2
# => ["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") }

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:

# => [1,2,3]
users = User.shire
# => [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)
# => 2
# => ["[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 pluckpluck(: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)
# => ["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) )
# => "Frodo Baggins"

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

users = User.where( User.arel_table[:home].not_eq("Shire") )
# => "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


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.

#activerecord#arel#article#basic#basics#blog#database#example#find#ids#learn#not#pluck#post#queries#query#rails#ruby on rails#where


  1. Leung Ho Kuen
    February 6, 2015 - 2:40 am

    I think you should use `User.shire` instead of `User.shire.ids` in `Email.where`,
    since that way you don’t need to load ids into memory, but only generate a query with sub-query to get the data.

    I only use ids to workaround some very complicated `or`, and small in number.
    But for simple `or` I most of the time use the logic (A OR B) == !(!A AND !B)

    • Daniel P. Clark
      February 6, 2015 - 10:33 am

      Thanks for the tip! In my mental model (the way I see it in my head) using User.shire was loading the complete Objects, parameters and all. So it’s good to know otherwise.

      • Leung Ho Kuen
        February 6, 2015 - 6:17 pm

        I am not sure how that used to work in rails 3, but in rails 4 most of the time you can keep passing the Relation into another `where` and it will generate a query with sub-query for you.

        Also, if you want to select parent only with children association records, you can use `select` instead of `pluck`.
        For example, if `Post` `has_many` `Comment`, then using `Post.where(id: Comment.select(:post_id)` is better then Post.where(id: Comment.pluck(:post_id)`. The former also generates a query with sub-query.

        I use this “magic” heavily inside my model scopes.

        If you are using `rails-footnotes` though, you might have some performance impact when you have some relation assigned to ivar due to AR::Relation#inspect loading the records.
        I am not sure if this is the “right” behaviour, but can be worked around by NOT assigning a relation with no limit with it.
        See more in https://github.com/josevalim/rails-footnotes/issues/102.

        Too long, post first 😛

        • Daniel P. Clark
          February 6, 2015 - 7:09 pm

          I haven’t been in the practice of using select in terms of ActiveRecord as I think of it in terms of Array. I will look more into it.

      • Leung Ho Kuen
        February 6, 2015 - 6:31 pm

        In the section of Arel, I think it’s missing an example of really complicated query.
        And other examples can be achieved without Arel.

        I suggest adding an example of `or`, which is only supported in pure AR in Rails 5.

        Also Arel does NOT do magic type converting, if you provide some data with wrong type it would just complain.
        For example, `User.where(age: 33)` & `User.where(age: [33, 111])` are valid, while `User.where( User.arel_table[:age].eq([33, 111]) )` would raise exception.

        As a random thing I just remember, `Range` can be in AR as well so I use it for time related methods like `created_after(timestamp)` is actually `where(created_at: (timestamp..Time.now))`

        • Daniel P. Clark
          February 6, 2015 - 7:06 pm

          As you can see from my example I didn’t write

          User.where( User.arel_table[:age].eq([33, 111]) )

          since the eq() method is for a specific item. My example uses eq_any() to match any of the values

          User.where( User.arel_table[:age].eq_any([33,111]) )

          The github link I provided for Arel includes the details for other equating methods.

          If you want an example of or, which I do believe is most helpful, then here’s one I gave to a StackOverflow answer:


          or is amazing to have for querying as it merges both query results within one query. I have planned to get more into things like this in another post. I just wanted to put a teaser and some good resources up for people to discover it.

  2. Glenn Goodrich
    February 10, 2015 - 9:42 am

    By the way, since you use `count` above, there are implications of using `count` vs `size` on ActiveRecord::Relation that are summed up in this comment: http://batsov.com/articles/2014/02/17/the-elements-of-style-in-ruby-number-13-length-vs-size-vs-count/#comment-1248998887

    (I only recently found this out, but it’s great info)

    • Daniel P. Clark
      February 10, 2015 - 11:44 am

      Thanks for bringing that up! Yes I have actually run into an error recently when using count on a custom Arel query with acts_as_messagable. Changing count to size resolved the issue.

Leave a Reply

Your email address will not be published / Required fields are marked *