w3d4

Question 1

What do ActiveRecord querying methods return?

Answer 1

An instance of ActiveRecord::Relation

Question 2

When are Relation instances evaluated (i.e. a database query is fired)?

Answer 2

When they are directly accessed (i.e. via #each)

Question 3

What happens to a Relation after a query is run?

Answer 3

After the query is run, the results are cached by Relation; they are stored for later re-use. Subsequent calls to each will not fire a query; they will instead use the prior result.

Question 4

Say we want to re-fire a query after changing something in the db. What do we do?

Answer 4

Reload and ignore cached values by calling #query_method(true) on the instance in question.

Question 5

What happens when we call ::where on a Relation?

Answer 5

A new Relation object is returned that reflects the query (note, however, that it is the existing relation that is queried, not the db. Furthermore, the already existing relation remains unchanged.)

Question 6

What does #where_values do when called by a Relation?

Answer 6

It returns the results of the previous where query as a hash.

Question 7

What happens when a method like #count is called on a Relation?

Answer 7

It forces the evaluation of a query, unlike most other query methods.

Question 8

How can we force the evaluation of a query?

Answer 8

With #load.

Question 9

How do we prefetch an association, and why bother?

Answer 9

By using

self.table_name.includes(:association_name)

This allows us the fire off the query based on the defined association, and avoids overhead by needing to run the query later.

Question 10

What does prefetching association allow us to do?

Answer 10

Use iterative (N+1) methods on the prefetched data without the normal SQL query overhead.

For example, if we do the following:

comments = user.comments.includes(:post, :parent_comment)

Then all of the following will work without hitting the db:

comments[0].post
comments[0].parent_comment
comments.each do |comment|

Question 11

How can we do a nested prefetch (for multi-level associations?)

Answer 11

posts = user.posts.includes(:comments => [:author, :parent_comment])

Question 12

What does ::joins require?

Answer 12

An association given as a symbol argument.

Question 13

What does ::joins return, and how is this different from what ::includes returns?

Answer 13

joins does the opposite of includes:
# includes fetches the entries and the associated entries
both. User.joins(:comments) returns no Comment data, just
the User columns. For this reason, joins is used less
commonly than includes.

Question 14

What SQL operation does ::joins perform?

Answer 14

INNER JOIN

Question 15

When should we use ::joins as opposed to ::includes?

Answer 15

When we want to do an aggregation (i.e. count), as opposed to a retrieval of all column values.

Question 16

So, what is going on here?

posts_with_counts = self
.posts
.select(“posts., COUNT() AS comments_count”) # more in a sec
.joins(:comments)
.group(“posts.id”) # “comments.post_id” would be equivalent

Answer 16

We are creating an array of Post objects, but Rails is also able to dynamically add a new method to the returned Post objects, #comments_count, which stores the results of the SELECT clause which is also specified. Cool!

Question 17

What is going on here?

posts_with_counts = self
.posts
.select(“posts.*, COUNT(comments.id) AS comments_count”) # more in a sec
.joins(“LEFT OUTER JOIN comments ON posts.id = comments.post_id”)
.group(“posts.id”) # “comments.post_id” would be equivalent

Answer 17

We are performing a non-default LEFT OUTER JOIN to include posts with zero comments. Once again, Rails dynamically adds #comments_count to the returned Post objects.

Question 18

What is going on here?

start_time = (DateTime.now.midnight - 1.day)
end_time = DateTime.now.midnight
Comment.joins(:posts).where(
  'posts.created_at BETWEEN ? AND ?',
  start_time,
  end_time
).uniq

Answer 18

We are interpolating values into the where query to retrieve the unique posts from the previous day.

Question 19

What is a scope?

Answer 19

An ActiveRecord::Base class method which returns a relation.

Question 20

What is a dynamic finder and how do we use one?

Answer 20

Rails can figure out what to look for by making a method that builds on find_by

Application.find_by_email_address(“ned@appacademy.io”)

Question 21

How can we retrieve records in order?

Answer 21

Client.order(“orders_count ASC, created_at DESC”).all

Question 22

How can we use GROUP BY and HAVING clauses?

Answer 22

UserPost
.joins(:likes)
.group(“posts.id”)
.having(“COUNT(*) > 5”)

Question 23

How can we use our own SQL queries to access the db directly?

Answer 23

Use ::find_by_sql(«-SQL). This takes the standard heredoc format.

Question 24

When should we go into the db directly with ::find_by_sql ?

Answer 24

When our queries grow to a complexity that it becomes too much of a hassle to figure out in AR. AR is meant to ease your burden on easy queries, not add to it.

Question 25

How do we use ::find_by_sql with interpolated values?

Answer 25

Pass the arguments AND the query in an array:

Post.find_by_sql(
  [
    "SELECT title FROM posts WHERE author = ? AND created > ?",
    author_id,
    start_date
  ]
)

Question 26

What does ::find_by_sql return?

Answer 26

An array of objects.