Basic SQL and ActiveRecord for Rails

ActiveRecord is a wonderful database abstraction layer that is included in Rails and generally makes life a lot easier. ActiveRecord lets developers focus on solving business problems without worrying about common database queries on a day to day basis. However, a comprehensive understanding raw SQL database queries is still vital for developers and this post summarizes the basic SQL queries that are used in a standard Rails CRUD app.

Start by creating a Rails app called basic_sql_active_record and create a Post MVC with title and body attributes using scaffolding.

$ rails g scaffold Post title body:text
$ rake db:migrate

Seed the database with 100 posts.

# db/seeds.rb
100.times do |counter|
  Post.create! title: "Title #{counter + 1}", body: "Body: #{counter + 1}"
$ rake db:seed

Go to the /posts path (corresponds with PostsController#index) and look at the server log to see the query that was used to fetch all the posts.

SELECT "posts".* FROM "posts" 

Fetch the first post from the database, view the corresponding SQL statement that is used to fetch the record, and print the class of the object.

>> Post.first
  Post Load (0.3ms)  SELECT "posts".* FROM "posts" LIMIT 1
=> #<Post id: 1, title: "Title 1", body: "Body: 1", created_at: "2013-07-13 00:09:00", updated_at: "2013-07-13 00:09:00">
>> Post.first.class
=> Post

This example makes it seem like the database is actually storing objects, but this is not the case. The database only stores the data associated with the object and the objects are recreated with the data that is fetched from the database (this is a crude explanation of an Object Relational Mapping database).

Update the code so only the first 25 posts are shown on the index page and describe the corresponding SQL query.

# controllers/posts_controller.rb
def index
  @posts = Post.limit(25)

Reload the /posts page and look at the server log to see the query that fetched the records.

SELECT "posts".* FROM "posts" LIMIT 25

Update the code so posts 26-50 are fetched from the database (the technique for this query is similar to the technique used by pagination gems).

# controllers/posts_controller.rb
def index
  @posts = Post.limit(25).offset(25)

The OFFSET keyword is used to specify that the record retrieveal should begin after the 25th post.

SELECT "posts".* FROM "posts" LIMIT 25 OFFSET 25

Write a SQL query that fetches all posts with ids greater than 10 and less than or equal to 85.

SELECT "posts".* FROM "posts" WHERE (id > 10 AND id <= 85)

The ActiveRecord that generates the same query is as follows:

>> Post.where("id > ? AND id <= ?", 10, 85)

In the next blog post, we will talk about database queries that involve multiple tables.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s