Database query optimization in Ruby on Rails - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to fetch all users with a single query.

Ruby on Rails

users = User.[1]

Drag options to blanks, or click blank then click option'

Afind

Bwhere

Call

Dpluck

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'find' without an ID causes an error.

Using 'pluck' returns only specific columns, not full records.

The all method fetches all records in one query.

2fill in blank

medium

Complete the code to eager load associated posts with users.

Ruby on Rails

users = User.[1](:posts)

Drag options to blanks, or click blank then click option'

Agroup

Bjoins

Cselect

Dincludes

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'joins' does not eager load and can cause N+1 queries.

Using 'select' changes columns returned but does not eager load.

The includes method eager loads associations to reduce queries.

3fill in blank

hard

Fix the error in the query to find users older than 30.

Ruby on Rails

users = User.where("age [1] ?", 30)

Drag options to blanks, or click blank then click option'

A>

B!=

C<

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' returns only users exactly 30 years old.

Using '<' returns users younger than 30.

The operator > correctly filters users older than 30.

4fill in blank

hard

Fill both blanks to optimize fetching user emails for active users.

Ruby on Rails

emails = User.where(active: [1]).[2](:email)

Drag options to blanks, or click blank then click option'

Atrue

Bfalse

Cpluck

Dselect

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'false' fetches inactive users.

Using 'select' returns ActiveRecord objects, not just emails.

Filtering active users with true and using pluck fetches only emails efficiently.

5fill in blank

hard

Fill all three blanks to create a hash of user names and their post counts for users with more than 5 posts.

Ruby on Rails

result = User.joins(:posts).group([1]).having("COUNT(posts.id) [2] ?", [3]).count

Drag options to blanks, or click blank then click option'

A:name

B>

C5

D:email

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by :email changes the hash keys incorrectly.

Using '<' or '=' filters wrong post counts.

Grouping by :name, filtering with > 5 posts, and counting returns the desired hash.

Practice

1. What is the main benefit of using includes in Rails queries?

easy

A. It loads associated records in fewer database queries to improve performance.

B. It deletes unused records from the database automatically.

C. It encrypts data before saving to the database.

D. It creates new database tables for associations.

Solution

1. Step 1: Understand what includes does

   includes loads related records in one or fewer queries to avoid repeated database hits.

2. Step 2: Identify the benefit

   This reduces the number of queries, speeding up the app and saving server resources.

3. Final Answer:

   It loads associated records in fewer database queries to improve performance. -> Option A

4. Quick Check:

   Using includes reduces queries = B [OK]

Hint: Think: fewer queries means faster loading [OK]

Common Mistakes:

• Confusing includes with deleting records
• Thinking includes encrypts data
• Assuming includes creates tables

2. Which of the following is the correct syntax to select only the name and email columns from a User model in Rails?

easy

A. User.find(:name, :email)

B. User.where(:name, :email)

C. User.includes(:name, :email)

D. User.select(:name, :email)

Solution

1. Step 1: Recall the method to choose specific columns

   The select method specifies which columns to retrieve from the database.

2. Step 2: Check the syntax

   User.select(:name, :email) correctly selects only the name and email columns.

3. Final Answer:

   User.select(:name, :email) -> Option D

4. Quick Check:

   Select columns = select method = D [OK]

Hint: Use select to pick columns, not where or includes [OK]

Common Mistakes:

• Using where instead of select for columns
• Using includes to select columns
• Using find with multiple columns

3. Given the following Rails code:

posts = Post.includes(:comments).where(published: true)

What is the main effect of this query?

medium

A. It loads all posts and their comments, regardless of published status.

B. It loads only comments for published posts but not the posts themselves.

C. It loads only published posts and their comments efficiently in fewer queries.

D. It deletes unpublished posts and their comments.

Solution

1. Step 1: Analyze the query filters and includes

   The where(published: true) filters posts to only those published. includes(:comments) loads comments with posts efficiently.

2. Step 2: Understand the combined effect

   The query fetches published posts and their comments in fewer database queries, improving performance.

3. Final Answer:

   It loads only published posts and their comments efficiently in fewer queries. -> Option C

4. Quick Check:

   Filter + includes = efficient published posts with comments = C [OK]

Hint: Filter first, then includes loads related data efficiently [OK]

Common Mistakes:

• Thinking includes ignores the where filter
• Assuming comments load without posts
• Confusing includes with deletion

4. Identify the problem in this Rails query and how to fix it:

users = User.where(active: true).includes(:posts).select(:name)

medium

A. The select excludes the primary key (:id), causing posts not to load; fix by including :id in select.

B. The includes method is used incorrectly; replace it with joins.

C. The where clause should come after select to filter correctly.

D. The query will delete inactive users; remove where clause.

Solution

1. Step 1: Understand select limits columns

   The select(:name) limits columns returned, but the primary key id is missing.

2. Step 2: Effect on includes

   Without the primary key, Rails cannot load associated posts properly with includes.

3. Step 3: Fix the query

   Add the primary key column (e.g., :id) to the select list to allow loading posts.

4. Final Answer:

   The select excludes the primary key (:id), causing posts not to load; fix by including :id in select. -> Option A

5. Quick Check:

   Select must include primary key for includes to work = A [OK]

Hint: Always select primary keys when using includes [OK]

Common Mistakes:

• Removing primary key from select breaks associations
• Replacing includes with joins unnecessarily
• Changing where order incorrectly

5. You want to optimize a query to fetch Order records with their Customer and only the total_price and created_at fields from orders. Which Rails query is best for this optimization?

hard

A. Order.joins(:customer).select('orders.total_price, orders.created_at, customers.*')

B. Order.includes(:customer).select(:total_price, :created_at, :customer_id)

C. Order.includes(:customer).select(:total_price, :created_at)

D. Order.all.select(:total_price, :created_at)

Solution

1. Step 1: Identify needed fields and associations

   You want orders with only total_price and created_at, plus associated customers.

2. Step 2: Ensure foreign key is selected for includes

   Using includes(:customer) requires selecting the foreign key customer_id to load customers efficiently.

3. Step 3: Choose the query that selects needed fields and includes association correctly

   Order.includes(:customer).select(:total_price, :created_at, :customer_id) meets all requirements.

4. Final Answer:

   Order.includes(:customer).select(:total_price, :created_at, :customer_id) -> Option B

5. Quick Check:

   Select needed fields + foreign key + includes = A [OK]

Hint: Include foreign keys in select when using includes [OK]

Common Mistakes:

• Omitting foreign key breaks includes
• Using joins loads all customer fields unnecessarily
• Selecting all orders without includes