Where conditions in Ruby on Rails - Step-by-Step Execution

Concept Flow - Where conditions

Start Query

↓

Apply Where Condition

↓

Build SQL WHERE Clause

↓

Execute Query

↓

Return Filtered Records

↓

End

Rails builds a SQL query by applying where conditions, then executes it to return filtered records.

Execution Sample

Ruby on Rails

User.where(age: 30)
User.where("name = ?", "Alice")
User.where(age: 20..30)

These lines filter users by age equal to 30, name equal to 'Alice', and age between 20 and 30.

Execution Table

Step	Where Condition	SQL WHERE Clause	Action	Result
1	age: 30	WHERE age = 30	Build query with age filter	Query ready with age=30 filter
2	Execute query	WHERE age = 30	Run SQL query	Returns users with age 30
3	name = ?	WHERE name = 'Alice'	Build query with name filter	Query ready with name='Alice' filter
4	Execute query	WHERE name = 'Alice'	Run SQL query	Returns users named Alice
5	age: 20..30	WHERE age BETWEEN 20 AND 30	Build query with age range filter	Query ready with age between 20 and 30
6	Execute query	WHERE age BETWEEN 20 AND 30	Run SQL query	Returns users aged 20 to 30
7	End	-	-	No more queries

💡 All where conditions applied and queries executed to return filtered results.

Variable Tracker

Variable	Start	After 1	After 3	After 5	Final
query	User.all	User.where(age: 30)	User.where("name = ?", "Alice")	User.where(age: 20..30)	Queries executed and results returned

Key Moments - 3 Insights

Why does User.where(age: 30) generate SQL with 'age = 30'?

Because the where method converts the hash key and value into a SQL equality condition, as shown in execution_table row 1.

How does User.where("name = ?", "Alice") prevent SQL injection?

It uses parameter binding with placeholders, safely inserting 'Alice' into the query, as shown in execution_table row 3.

What happens when a range like 20..30 is used in where?

Rails converts it to a BETWEEN SQL clause to filter values within the range, as shown in execution_table row 5.

Visual Quiz - 3 Questions

Look at the execution_table, what SQL clause is generated at step 5?

A WHERE age = 30

B WHERE age BETWEEN 20 AND 30

C WHERE name = 'Alice'

D WHERE age > 30

💡 Hint

Check the 'SQL WHERE Clause' column at step 5 in the execution_table.

At which step does the query filter users by name?

A Step 3

B Step 5

C Step 1

D Step 7

💡 Hint

Look for the where condition with 'name = ?' in the execution_table.

If we change User.where(age: 30) to User.where(age: 25), how does the execution_table change?

A Step 3 SQL clause becomes 'WHERE name = 25'

B Step 5 SQL clause becomes 'WHERE age BETWEEN 25 AND 30'

C Step 1 SQL clause becomes 'WHERE age = 25'

D No change in the execution_table

💡 Hint

Focus on the SQL WHERE Clause in step 1 related to age filtering.

Concept Snapshot

Rails 'where' filters records by building SQL WHERE clauses.
Use hash for simple equality: where(age: 30) -> WHERE age = 30.
Use placeholders for safety: where("name = ?", "Alice").
Use ranges for BETWEEN: where(age: 20..30).
Returns filtered ActiveRecord::Relation, query runs on execution.

Full Transcript

In Rails, the 'where' method adds conditions to filter database records. It converts Ruby hashes or strings with placeholders into SQL WHERE clauses. For example, where(age: 30) becomes WHERE age = 30 in SQL. Using placeholders like where("name = ?", "Alice") safely inserts values to prevent SQL injection. Ranges like 20..30 become BETWEEN clauses. The query builds step-by-step and runs when needed, returning filtered results.

Practice

1. What does the where method do in Rails ActiveRecord?

easy

A. Creates a new database table

B. Deletes records from the database

C. Updates all records without conditions

D. Filters records based on given conditions

Solution

1. Step 1: Understand the purpose of where

   The where method is used to specify conditions to filter records in a database query.

2. Step 2: Compare with other database actions

   Deleting, creating tables, or updating all records are different methods like delete, migrations, or update_all.

3. Final Answer:

   Filters records based on given conditions -> Option D

4. Quick Check:

   where filters records = C [OK]

Hint: Remember: where narrows down records by conditions [OK]

Common Mistakes:

• Confusing where with delete or update methods
• Thinking where creates or deletes tables
• Assuming where modifies all records without conditions

2. Which of the following is the correct syntax to find users with age 30 using where?

easy

A. User.where('age == ?', 30)

B. User.where(age: 30)

C. User.where(age == 30)

D. User.where(age = 30)

Solution

1. Step 1: Identify correct hash syntax for where

   Using a hash like age: 30 is the standard way to filter by attribute value.

2. Step 2: Check other options for syntax errors

   User.where('age == ?', 30) uses invalid SQL operator ==; User.where(age == 30) uses invalid Ruby comparison inside where; User.where(age = 30) uses assignment instead of comparison.

3. Final Answer:

   User.where(age: 30) -> Option B

4. Quick Check:

   Hash syntax for where = A [OK]

Hint: Use hash syntax for simple where conditions [OK]

Common Mistakes:

• Using == inside where instead of hash or string
• Using = instead of : or ? for conditions
• Confusing string and hash syntax

3. What will this code return?

User.where("name LIKE ?", "%John%")

medium

A. All users with name containing 'John'

B. All users with name exactly 'John'

C. Syntax error due to quotes

D. No users because LIKE is invalid

Solution

1. Step 1: Understand the SQL LIKE operator

   LIKE with % means match any characters before or after 'John', so it finds names containing 'John'.

2. Step 2: Check the where syntax

   The string with placeholder and argument is correct syntax for complex conditions.

3. Final Answer:

   All users with name containing 'John' -> Option A

4. Quick Check:

   LIKE with % means contains = A [OK]

Hint: LIKE with % means contains substring [OK]

Common Mistakes:

• Thinking LIKE matches exact string only
• Confusing syntax with invalid quotes
• Assuming LIKE is not supported in where

4. Identify the error in this code:

User.where(age > 25)

medium

A. Incorrect use of > inside where without string

B. Should use User.find instead of where

C. No error, code works fine

D. Missing quotes around condition string

Solution

1. Step 1: Check syntax of where conditions

   Conditions with operators like > must be inside a string or hash syntax; direct Ruby expressions are invalid.

2. Step 2: Identify correct usage

   Correct usage is User.where("age > ?", 25) or User.where("age > 25").

3. Final Answer:

   Incorrect use of > inside where without string -> Option A

4. Quick Check:

   Operators need string syntax in where = D [OK]

Hint: Use strings for operators like > in where [OK]

Common Mistakes:

• Writing Ruby expressions directly inside where
• Forgetting to quote conditions with operators
• Using find instead of where for filtering

5. How can you chain multiple where conditions to find users who are active and older than 18?

hard

A. User.where(active: true, age > ?, 18)

B. User.where('active = true AND age > ?')

C. User.where(active: true).where('age > ?', 18)

D. User.where(active == true).where(age > 18)

Solution

1. Step 1: Understand chaining of where

   Multiple where calls can be chained to combine conditions, each filtering further.

2. Step 2: Check syntax of each where call

   First where uses hash for simple boolean, second uses string with placeholder for age condition.

3. Step 3: Evaluate other options

   User.where(active: true, age > ?, 18) uses invalid Ruby expression without quotes; User.where('active = true AND age > ?') uses placeholder without bind argument; User.where(active == true).where(age > 18) uses invalid Ruby expressions.

4. Final Answer:

   User.where(active: true).where('age > ?', 18) -> Option C

5. Quick Check:

   Chain where with hash and string = B [OK]

Hint: Chain where calls for multiple conditions [OK]

Common Mistakes:

• Mixing hash and string in one where call incorrectly
• Using == inside where instead of : or placeholders
• Not chaining where calls properly