Learn Rails - ActiveRecord Queries
| Question | Click to View Answer |
Create a new rails project called database_queries and then create a Post MVC with title and body attributes using scaffolding. |
$ rails new database_queries
$ cd database_queries
$ rails generate scaffold Post title:string body:text
|
Create the posts table in the database. |
$ rake db:migrate
|
Create 3,000 posts and indicate the post number in the title and body (i.e. "this is the title for post 567" and "this is the body for post 567"). |
# db/seeds.rb
3_000.times do |counter|
Post.create(:title => "This is the title for post #{counter + 1}", :body => "This is the body for post #{counter + 1}")
end
```ruby
$ rake db:seed ``` |
Use Rails console to find the post with id = 489. |
Post.find(489)
|
Find the last post. |
Post.last
|
Find the first post. |
Post.first
|
Find the posts with ids of 478 and 1134. |
Post.find(478, 1134)
# OR
Post.find([478, 1134])
|
Find the posts with ids from 100 to 105. |
Post.find((100..105).to_a)
|
Add a comments column to the posts table that will display the number of comments on a given post and update the database. |
$ rails g migration AddCommentsToPosts comments:integer
$ rake db:migrate
|
Add a rake task that goes through all the model validations, iterates over all the posts, and update the number of comments to be a random number between 1 and 100. |
# lib/tasks/post.rake
namespace :post do
desc "updates comments in posts table"
task :update_comments => :environment do
Post.find_each do |post|
post.comments = rand(1..100)
post.save!
end
end
end
Run the rake task with this command:
|
What methods should be used when iterating over large data sets. |
When iterating over data sets with more than 1000 entries, find_each or find_in_batches should be used. These methods retrieve 1000 records at a time by default and will not overwhelm memory. The all method puts all the records in memory and can be inefficient or cause the computer to crash if there is not enough room in memory. |
Select all posts with more than 97 comments. |
Post.where("comments > ?", 97)
|
Use an inefficient method to select all comments with more than 97 comments and compare how much longer it takes the inefficient method to perform this calculation than the efficient method used in the prior question. |
Post.all.select {|post| post.comments > 97}
Underneath the Rails console statement, you can see how long it took to execute the query. On my computer, the inefficient method took 14.4ms and the efficient method took 2.8ms. |
Select all posts with 45 comments. |
Post.where(:comments => 45)
# OR
Post.where("comments = ?", 45)
|
Order the posts with the oldest posts on top. |
Post.order("created_at DESC")
|
Order the posts from the posts with the least comments to the posts with the most comments. |
Post.order("comments ASC")
|
Show the number of comments on the first 100 posts. |
Post.select("comments").limit(100)
|
Select the first 20 posts. |
Post.limit(20)
|
Select the first 25 posts starting at the 500th post. |
Post.limit(25).offset(500)
|
Sort the posts by the number of comments each post has. |
Post.group("comments")
|
Generate a migration to add a users table with a name attribute and add a user_id column to the posts table. Run the migration. |
class User < ActiveRecord::Migration
def change
create_table :users do |t|
t.string :name
t.timestamps
end
add_column :posts, :user_id, :integer
end
end
$ rake db:migrate
|
Associate the User and Post models. |
# models/post.rb
class Post < ActiveRecord::Base
attr_accessible :body, :title, :comments, :user_id
belongs_to :user
end
# models/user.rb
class User < ActiveRecord::Base
attr_accessible :name
has_many :posts
end
|
Add rake tasks to create 10 fake users and assign each post a user_id that is a random number between 1 and 10. |
# lib/tasks/user.rake
namespace :user do
desc "creates 10 fake users"
task :fake_users => :environment do
10.times do |number|
User.create!(:name => "User #{number + 1}")
end
end
end
lib/tasks/post.rake
namespace :post do
desc "assigns random user_ids"
task :random_user_ids => :environment do
Post.find_each do |post|
post.user_id = rand(1..10)
post.save!
end
end
end
|
What is the problem with the following code? posts = Post.limit(10)
posts.each do |post|
puts post.user
end
|
This code makes 11 database queries (this is referred to as the n + 1 problem), so it is highly inefficient. If you look at the SQL queries, you will also notice that some users are loaded twice or more. |
Refactor the code from the previous example, so only one database query is made. |
posts = Post.includes(:user).limit(10)
posts.each do |post|
puts post.user
end
This is called eager loading. |
Create a scope called popular that returns all posts with more than 97 comments. Use this scope in Rails console. |
# models/post.rb:
scope :popular, where("comments > ?", 97)
# Rails console
Post.popular
|
Update the first 100 posts with a rake task so the created at date is set to some time in 2008. |
# lib/tasks/post.rake
namespace :post do
desc "post rake tasks including changing date"
task :change_date => :environment do |t, args|
d = DateTime.new(2008, 05, 02)
Post.limit(100).each_with_index do |post, index|
new_day = d + index.day
post.update_attributes(:created_at => new_day)
end
end
end
# models/post.rb
attr_accessible :body, :title, :comments, :user_id, :created_at
|
Create a scope called old that pulls posts that are older than 2 years old. |
# models/post.rb
scope :old, lambda { where("created_at < ?", Time.now - 2.year) }
lambda is used so the scope is evaluated every time. Otherwise, the scope will only be evaluated once and it will miss posts that became older than 2 years as time passes. # Rails console
Post.old
|
Make it so all queries exclude the posts with ids less than 100. |
# models/post.rb
default_scope where("id > ?", 100)
|
Keep the code that was added in the last question and return all posts with ids between 50 and 60. |
Post.unscoped.where("id > ? AND id < ?", 50, 60)
# OR
Post.unscoped.find((50..60).to_a)
|
Use a dynamic finder to find the post with the title "This is the title for post 104". |
Post.find_by_title("This is the title for post 104")
|
Use a dynamic finder to locate the posts that have 45 comments. |
Post.unscoped.find_all_by_comments(45)
|
Search for a post with a title equal to "Sublime" and if it does not exist, create a new post with a title of "Sublime" and a body of "Best band ever". |
Post.where(:title => "Sublime").first_or_create(:body => "Best band ever")
|
Use SQL in Rails console to find all posts with more than 98 comments. |
Post.find_by_sql("SELECT * FROM posts WHERE comments > 98")
|
Return the number of posts. |
Post.count
Also try Post.all.length to see how much faster Post.count is. |
Find the smallest number of comments that have been made on any post. |
Post.minimum("comments")
|
Find the total number of comments that have been made on all posts. |
Post.sum("comments")
|