Query interface basics in Ruby on Rails - Mini Project: Build & Apply

Query Interface Basics in Rails

📖 Scenario: You are building a simple Rails app to manage a bookstore's inventory. You want to find books based on their price and availability.

🎯 Goal: Learn how to use Rails query interface methods to filter and retrieve records from the database.

📋 What You'll Learn

Create a Book model with specific attributes

Set a price threshold variable

Use ActiveRecord query methods to find books cheaper than the threshold

Add a condition to find only books that are in stock

💡 Why This Matters

🌍 Real World

Filtering and searching records is a common task in web apps like online stores or libraries.

💼 Career

Understanding Rails query interface is essential for backend developers working with databases.

1

Create the Book model with attributes

Create a Rails model called Book with attributes title (string), price (integer), and in_stock (boolean). Then create three books with these exact values: 'Ruby Basics' priced at 30 and true for in_stock, 'Rails Advanced' priced at 50 and false for in_stock, and 'JavaScript Guide' priced at 20 and true for in_stock.

Ruby on Rails

# Define Book model and create three books with given attributes
# Your code here

Hint

Use Book.create to add records with the exact attribute values.

2

Set a price threshold variable

Create a variable called max_price and set it to 40. This will be used to find books cheaper than this price.

Ruby on Rails

class Book < ApplicationRecord
end

Book.create(title: 'Ruby Basics', price: 30, in_stock: true)
Book.create(title: 'Rails Advanced', price: 50, in_stock: false)
Book.create(title: 'JavaScript Guide', price: 20, in_stock: true)

# Create max_price variable and set to 40
# Your code here

Hint

Just assign the number 40 to a variable named max_price.

3

Find books cheaper than max_price

Use the Rails query interface to find all books with price less than max_price. Assign the result to a variable called cheap_books. Use where with a condition on price.

Ruby on Rails

class Book < ApplicationRecord
end

Book.create(title: 'Ruby Basics', price: 30, in_stock: true)
Book.create(title: 'Rails Advanced', price: 50, in_stock: false)
Book.create(title: 'JavaScript Guide', price: 20, in_stock: true)

max_price = 40

# Find books with price less than max_price and assign to cheap_books
# Your code here

Hint

Use Book.where('price < ?', max_price) to filter books cheaper than max_price.

4

Find books cheaper than max_price and in stock

Update the query to find books with price less than max_price and in_stock equal to true. Assign the result to a variable called available_cheap_books. Use where with multiple conditions.

Ruby on Rails

class Book < ApplicationRecord
end

Book.create(title: 'Ruby Basics', price: 30, in_stock: true)
Book.create(title: 'Rails Advanced', price: 50, in_stock: false)
Book.create(title: 'JavaScript Guide', price: 20, in_stock: true)

max_price = 40

cheap_books = Book.where('price < ?', max_price)

# Find books with price less than max_price and in_stock true, assign to available_cheap_books
# Your code here

Hint

Use where with SQL conditions joined by AND to filter by price and stock.

Practice

1. What does the where method do in Rails' query interface?

easy

A. Filters records based on given conditions

B. Deletes records from the database

C. Updates records with new values

D. Creates new records in the database

Solution

1. Step 1: Understand the purpose of where

   The where method is used to filter records by specifying conditions, like filtering users by age.

2. Step 2: Compare with other methods

   Methods like delete, update, and create perform different actions, not filtering.

3. Final Answer:

   Filters records based on given conditions -> Option A

4. Quick Check:

   where filters records [OK]

Hint: Remember: where = filter records by condition [OK]

Common Mistakes:

• Confusing where with update or delete
• Thinking where creates or deletes records
• Assuming where changes data instead of filtering

2. Which of the following is the correct syntax to get all users ordered by their created_at date in descending order?

easy

A. User.order(:created_at, :desc)

B. User.order('created_at ASC')

C. User.order(created_at: :desc)

D. User.order(created_at => 'desc')

Solution

1. Step 1: Identify correct hash syntax for order

   Rails expects a hash with column name as key and direction as symbol, like created_at: :desc.

2. Step 2: Check other options for syntax errors

   User.order(:created_at, :desc) uses wrong argument format treating :desc as column name, User.order('created_at ASC') uses string with wrong direction, User.order(created_at => 'desc') uses wrong hash rocket syntax.

3. Final Answer:

   User.order(created_at: :desc) -> Option C

4. Quick Check:

   Correct hash syntax for order = User.order(created_at: :desc) [OK]

Hint: Use symbol keys and symbols for direction in order [OK]

Common Mistakes:

• Passing multiple arguments instead of a hash
• Using wrong direction like ASC in order string
• Wrong hash syntax with => and string

3. Given the code Product.where(price: 100).limit(3).pluck(:name), what will be the output?

medium

A. An array of up to 3 product names with price 100

B. An array of all product names regardless of price

C. A single product name with price 100

D. An error because limit cannot be chained after where

Solution

1. Step 1: Understand chaining of query methods

   where(price: 100) filters products with price 100, limit(3) restricts to 3 records, pluck(:name) extracts names into an array.

2. Step 2: Analyze output type

   The result is an array of names, up to 3, matching the price condition.

3. Final Answer:

   An array of up to 3 product names with price 100 -> Option A

4. Quick Check:

   where + limit + pluck = filtered limited array [OK]

Hint: Chain filters, limits, then pluck for array output [OK]

Common Mistakes:

• Thinking pluck returns a single value
• Assuming limit cannot be chained after where
• Ignoring the filter condition in output

4. What is wrong with this query? User.where('age > ?', 18).order('name ASC').limit('five')

medium

A. The where clause syntax is incorrect

B. The limit argument should be an integer, not a string

C. The order method cannot use strings

D. The query is correct and will run without errors

Solution

1. Step 1: Check each method argument type

   where('age > ?', 18) is correct, order('name ASC') accepts string, but limit('five') expects an integer, not a string.

2. Step 2: Identify the error source

   Passing 'five' as a string sets limit to 0 because 'five'.to_i == 0, not the intended value.

3. Final Answer:

   The limit argument should be an integer, not a string -> Option B

4. Quick Check:

   limit needs integer, not string [OK]

Hint: limit must be a number, not a word string [OK]

Common Mistakes:

• Passing string instead of integer to limit
• Thinking order cannot take strings
• Misreading where clause syntax

5. You want to find all Article records published after 2020, sorted by title ascending, but only show the first 5 titles. Which query is correct?

hard

A. Article.where(published_at: > Date.new(2020,1,1)).order(:title).limit(5).pluck(:title)

B. Article.limit(5).order(title: :asc).where('published_at > 2020-01-01').pluck(:title)

C. Article.where('published_at > 2020-01-01').order('title DESC').limit(5).pluck(:title)

D. Article.where('published_at > ?', Date.new(2020,1,1)).order(title: :asc).limit(5).pluck(:title)

Solution

1. Step 1: Check filtering condition syntax

   Article.where('published_at > ?', Date.new(2020,1,1)).order(title: :asc).limit(5).pluck(:title) uses correct SQL placeholder with where('published_at > ?', Date.new(2020,1,1)). Article.where(published_at: > Date.new(2020,1,1)).order(:title).limit(5).pluck(:title) has invalid Ruby syntax. Article.where('published_at > 2020-01-01').order('title DESC').limit(5).pluck(:title) uses string date without quotes and wrong order direction.

2. Step 2: Verify order and limit usage

   Article.where('published_at > ?', Date.new(2020,1,1)).order(title: :asc).limit(5).pluck(:title) orders by title ascending with symbol and limits to 5 records before pluck. Article.limit(5).order(title: :asc).where('published_at > ?', '2020-01-01').pluck(:title) applies limit before where, which is less efficient and may give wrong results.

3. Final Answer:

   Article.where('published_at > ?', Date.new(2020,1,1)).order(title: :asc).limit(5).pluck(:title) -> Option D

4. Quick Check:

   Correct where, order ascending, limit 5, pluck titles [OK]

Hint: Filter first, then order, limit, and pluck [OK]

Common Mistakes:

• Wrong where syntax with > operator
• Ordering descending instead of ascending
• Limiting before filtering causing wrong results