Home » Code » Querying with Active Record in Ruby on Rails

Querying with Active Record in Ruby on Rails

Posted by on December 2nd, 2015

Querying with Active Record in Ruby on Rails
The Active Record pattern encapsulates a single row of the database in an object.

This makes it really easy to create, update, and delete data from the database.

We can also use the Active Record class to query data from the database. The ability to query, create, update, and delete from a single object is one of the reasons why Active Record makes working with the database in web applications so easy!

In today’s tutorial we will be looking at how to use the various query methods of Active Record.

Why is using Active Record for querying a benefit?

If you are new to Rails or using an ORM like Active Record, you might not be totally convinced that using Active Record to perform queries is really that good.

Traditionally, in order to query data from a database, a developer would need to write SQL. SQL is not that difficult to work with, but it is yet another thing you need to learn.

SQL is also something that is easy to get wrong. It’s not uncommon to see really bad SQL for very common operations that could be dramatically simplified.

Each different type of database also usually has a slightly different flavour of SQL, this means if you write your SQL for MySQL, it’s probably not going to be very easy to switch to a different database.

Active Record solves all of these problems.

Firstly, calling methods of an object is much easier than writing your own SQL statements. This makes the learning curve of querying data from the database much easier, particular when dealing with table joins.

The majority of common queries can be dealt with automatically by Active Record. This allows Active Record to write the best query for these common operations, without the developer having to worry about it.

And finally, Active Record is an abstraction that sits above the SQL layer. This means if you were to switch the underlying database engine, you wouldn’t have to change your code.

Finding a single record

Probably the most common Active Record query method you will see will be the find method:

user = User.find(123)

This will find the user in the database with an id of 123.

You can also pass an array to this method in order to find multiple records in one go:

users = User.find([4,5,6])

Finding the First or Last records

If you want to find the first record you can use the first method:

user = User.first

Similarly, if you want to find the last record you can use the last method:

user = User.last

Both of these methods also take an argument to return N records. For example, to get the first 10 records, you could use:

user = User.first(10)

And to get the last 10 records you could use:

user = User.last(10)

Declaring Conditions

Another common operation is to define WHERE clauses to find records that satisfy certain conditions.

We can use the where method to add conditions to the query:

articles = Article.where(published: true)

Here I’m selecting all of the articles that are set to published.

You can also pass an array to the conditions hash to perform a WHERE IN query:

articles = Article.where(status: ["publish", "draft"])

To perform a WHERE NOT query, you can use the following syntax:

articles = Article.where.not(published: true)

Ordering

You can specify an order in which you want the records to be returned by using the order method:

articles = Article.order(published_at: :desc)

In this example I’m passing a hash (Working with Hashes in Ruby) of the column to order by and a symbol (What are Symbols in Ruby?) for the direction. If you wanted to order the articles in the opposite direction you could use the :asc symbol.

You can also order by multiple fields by passing multiple values:

articles = Article.order(title: :asc, published_at: :desc)

Limit and Offset

Once you start to have a lot of records in your database you will want to start limiting the results that are returned on each request.

To limit the returned results you can use the limit method:

articles = Article.limit(10)

The code above will return the first 10 articles.

When working with pagination you will want to use limit in conjunction with offset to select a specific set of records:

articles = Article.limit(10).offset(10)

The above code will return 10 records starting at the 11th record.

Aggregates

A common mistake of newbie developers is trying to do calculations in Ruby that would be much better suited to be run in the database.

For example, if you need to count all of the records of a table, it’s much easier to push that to the database, rather than loading each record as an Active Record object, then counting the number of objects.

To count the number of records, you can use the count method:

Article.count

You can also call the count method after adding a WHERE clause:

article.where(published: true).count

As you would probably expect, you can also perform other aggregate queries in much the same way:

Article.average(:rating)

Article.minimum(:rating)

Article.maximum(:rating)

Article.sum(:rating)

Exists

Another common type of query is checking whether a record exists. Again we can perform this query in the database and simply return a boolean value, rather than having the overhead of hydrating the Active Record object:

Article.where(slug: "hello-world").exists?

This will return true if an article exists with a slug of “hello-world”.

Alternatively you can pass the arguments to the method itself:

Article.exists?(slug: "hello-world")

Associations

One of the best things about using Active Record for querying is that it will make it a lot easier to query associations. We looked at using Active Record Associations in Understanding Active Record Associations.

To perform an INNER JOIN query you can use the joins method:

Article.joins(:users)

Another common issue with querying associations is the “N + 1 Problem”. This is where you lazily load the required data causing N + 1 queries. For example, if you wanted to load 100 posts and their comments, you would end up performing 101 queries.

To prevent this issue and instead load the data in 2 queries, we can use Active Record’s eager loading:

articles = Article.includes(:comments)

Now when you access the comments on each Article object, the comments will already be loaded and thus will prevent any further queries from being performed.

Scopes

If your application requires that you add common clauses to a lot of your queries you can define a scope on the Active Record model to reduce this duplication:

class Article < ActiveRecord::Base
  scope :published, -> { where(published: true) }
end

Here we have defined a published scope to only return articles that have been marked as published.

Now you can use the published method on the model to use this scope:

articles = Article.published

Conclusion

I’ve only briefly touched upon the basics of querying data with Active Record.

Active Record is such a big topic there is an endless amount of functionality to cover.

As we continue on in this journey through Rails, no doubt we will cover a lot more of the nuances of using Active Record.

Active Record is really powerful and it’s honestly a joy to work with, I’m sure you will find it very easy to work with too!

Philip Brown

Hey, I'm Philip Brown, a designer and developer from Durham, England. I create websites and web based applications from the ground up. In 2011 I founded a company called Yellow Flag. If you want to find out more about me, you can follow me on Twitter or Google Plus.

  • Pingback: Defining URL routes in Ruby on Rails | Culttt()

  • Thanks Philip! There some typos in your article. Where your showing code samples for aggregates, the ‘a’ in ‘article’ needs to be capitalized (i.e., `

    article.sum(:rating)` should be `

    Article.sum(:rating)`.

  • What I like especially about ActiveRecord is how it allows you to introduce more DRY-ness through scopes and the flexibility of relations.

    AR however is not a silver bullet. It’s still possible to write horrible queries if you’re not careful.

    For performant use of AR, it’s good to have good knowledge of SQL and what kind of queries AR generates.

    It’s also important to know which methods affect a query, and which methods operate on the results of a query, that line is often blurred by AR.

    Perhaps these points become more relevant for a little more advanced uses, but I felt like I had to make this point. I loved the rest of your post, it nicely summarises the AR features!

    • Thanks Jeroen :)

      Yes indeed, you can definitely go wrong with Active Record!

      And yes I totally agree that newbie developers are often shielded from fully understanding the line of separation between Ruby and the database. I often see code that shows the developer does not understand what is best done in Ruby, and what is best done in the database.

      But I guess you have to go through that pain to fully understand how things really work under the surface :)

  • jen

    thanks Philip for your articles on Rails. I was having a hard time trying to understand active record until I found your blog. thanks a million times!!

  • Abdul Wahed

    thanks !! it was really helpful. there is an error-

    article.where(published: true).count
    should be Article.where(published: true).count