Ask LSBot

Querying the Database

Let's dive a little deeper into ActiveRecord and see how we can find and search for records:

### Lookups ###

# find the "first" `Post`
# notice the ORDER BY and LIMIT in the generated SQL
Post.first
#  Post Load (0.1ms)  SELECT  "posts".* FROM "posts"  ORDER BY "posts"."id" ASC LIMIT 1
#=> #<Post:0x000000059f3378
# id: 1,
# title: "Blog 1",
# body: "Lorem ipsum dolor sit amet.",
# author: "Kevin",
# created_at: Sun, 07 Dec 2014 00:00:00 UTC +00:00,
# updated_at: Sun, 07 Dec 2014 00:00:00 UTC +00:00>

# some similar methods...

Post.second
#  Post Load (0.1ms)  SELECT  "posts".* FROM "posts"  ORDER BY "posts"."id" ASC LIMIT 1 OFFSET 1
#=> #<Post:0x00000005a8b100 id: 2, ...

Post.last
#  Post Load (0.4ms)  SELECT  "posts".* FROM "posts"  ORDER BY "posts"."id" DESC LIMIT 1
#=> #<Post:0x00000005ae99f8 id: 3, ...

# we can even get counts
Post.count
#  (0.1ms)  SELECT COUNT(*) FROM "posts"
# => 3


# at this point we're familiar with the idea of `.find`
Post.find 1
#  Post Load (0.1ms)  SELECT  "posts".* FROM "posts" WHERE "posts"."id" = ? LIMIT 1  [["id", 1]]
#=> #<Post:0x00000005b86f50 id: 1, ...

# but with Active Record, we can also look up by several ids at once
Post.find [1,2]
#  Post Load (0.4ms)  SELECT "posts".* FROM "posts" WHERE "posts"."id" IN (1, 2)
#=> [#<Post:0x00000005bd8558 id: 1, ...>,
# #<Post:0x00000005bd83f0 id: 2, ...>]


# we're also familiar with `.where`
Post.where(author: 'Brad')
#  Post Load (0.3ms)  SELECT "posts".* FROM "posts" WHERE "posts"."author" = ?  [["author", "Brad"]]
#=> [#<Post:0x0000000207b528 ... author: "Brad", ...

# but we can also use special finders like this
Post.find_by_author 'Brad'
#  Post Load (0.1ms)  SELECT  "posts".* FROM "posts" WHERE "posts"."author" = ? LIMIT 1  [["author", "Brad"]]
#=> #<Post:0x00000005c6ee68 ... author: "Brad", ...

### ActiveRecord_Relation ###

# it's important to note what `.where` really gives us
Post.where(author: 'Brad').class
# => Post::ActiveRecord_Relation

# this ActiveRecord_Relation lets us chain calls together

# for example, if we wanted, for some reason,
# the first three comments ordered by author,
# we could chain `.author` and `.limit` calls
Comment.order(:author).limit(3)
#  Comment Load (0.2ms)  SELECT  "comments".* FROM "comments"  ORDER BY "comments"."author" ASC LIMIT 3
# => [#<Comment:0x00000005f84f58
#   id: 2,
#   body: "Lorem ipsum dolor sit amet.",
#   author: "DHH",
#   post_id: 1,
#   created_at: Thu, 11 Dec 2014 00:00:00 UTC +00:00,
#   updated_at: Thu, 11 Dec 2014 00:00:00 UTC +00:00>,
#  #<Comment:0x00000005f84df0
#   id: 5,
#   body: "Lorem ipsum dolor sit amet.",
#   author: "DHH",
#   post_id: 2,
#   created_at: Sun, 14 Dec 2014 00:00:00 UTC +00:00,
#   updated_at: Sun, 14 Dec 2014 00:00:00 UTC +00:00>,
#  #<Comment:0x00000005f84c88
#   id: 7,
#   body: "Lorem ipsum dolor sit amet.",
#   author: "DHH",
#   post_id: 3,
#   created_at: Tue, 16 Dec 2014 00:00:00 UTC +00:00,
#   updated_at: Tue, 16 Dec 2014 00:00:00 UTC +00:00>]

# this is possible because each of these methods is...
Comment.order(:author).limit(3).class
# => Comment::ActiveRecord_Relation

# but when we also iterate over these objects as if they were arrays
Comment.order(:author).limit(3).map {|comment|
  [comment.author, comment.created_at]
}
#  Comment Load (0.9ms)  SELECT  "comments".* FROM "comments"  ORDER BY "comments"."author" ASC LIMIT 3
#=> [["DHH", Thu, 11 Dec 2014 00:00:00 UTC +00:00],
# ["DHH", Sun, 14 Dec 2014 00:00:00 UTC +00:00],
# ["DHH", Tue, 16 Dec 2014 00:00:00 UTC +00:00]]

# the chaining of these calls basically builds up a query
# and the SQL is generated and executed upon something like
# one of the following methods being called on the object:
#
# .first
# .last
# .all
# .each
#
# which is why our `.map` above works as we'd expect it to

For more resources on ActiveRecord models, validations, associations, and more:

This conversation with LSBot is temporary. Sign up for free to save your conversations with LSBot.

Hi! I'm LSBot. I'm here to help you understand this chapter content with fast, focused answers.

Ask me about concepts, examples, or anything you'd like clarified from this chapter. I can explain complex topics, provide examples, or help connect ideas.

Want to know more? Refer to the LSBot User Guide.

This conversation with LSBot is temporary. Sign up for free to save your conversations with LSBot.

Hi! I'm LSBot. I'm here to help you think through this exercise by providing hints and guidance, without giving away the solution.

You can ask me about your approach, request clarification on the problem, or seek help when you feel stuck.

Want to know more? Refer to the LSBot User Guide.