Raw SQL when needed in Ruby on Rails - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output of this raw SQL query in Rails?

Given the following Rails code using raw SQL, what will be the output when fetching the user's name?

Ruby on Rails

user = User.find_by_sql(["SELECT name FROM users WHERE id = ?", 1]).first
user.name

A "Alice"

B nil

C 1

D ActiveRecord::RecordNotFound error

💡 Hint

The query selects the name column for user with id 1.

The raw SQL query returns an array of User objects with only the name attribute loaded. Accessing user.name returns the name string.

📝 Syntax

intermediate

Which option correctly uses raw SQL with parameter binding in Rails?

Select the option that safely executes a raw SQL query to find users older than a given age.

A User.find_by_sql("SELECT * FROM users WHERE age > 30")

B User.find_by_sql(["SELECT * FROM users WHERE age > ?", 30])

C User.find_by_sql(["SELECT * FROM users WHERE age > :age", {age: 30}])

D User.find_by_sql("SELECT * FROM users WHERE age > :age", age: 30)

💡 Hint

Look for the correct way to pass parameters safely to avoid SQL injection.

Option B uses the array form with ? placeholder and parameter binding, which is the correct and safe way.

🔧 Debug

advanced

Why does this raw SQL query raise an error in Rails?

Examine the code below and select the reason for the error raised.

Ruby on Rails

User.find_by_sql("SELECT * FROM users WHERE name = 'O'Reilly'")

A NoMethodError because find_by_sql is undefined

B ActiveRecord::RecordNotFound because no user named O'Reilly exists

C SyntaxError due to unescaped single quote in SQL string

D TypeError because argument is not an array

💡 Hint

Look at how the single quote in the name is handled inside the SQL string.

The single quote in O'Reilly breaks the SQL string syntax causing a syntax error.

❓ state_output

advanced

What is the value of 'count' after running this raw SQL in Rails?

Consider this code snippet. What will be the value of 'count'?

Ruby on Rails

result = ActiveRecord::Base.connection.select_all("SELECT COUNT(*) FROM users WHERE active = true")
count = result.first['count']

A An array of all active users

B An integer representing the number of active users

C nil because 'count' key does not exist

D A string representing the number of active users

💡 Hint

Check the data type returned by raw SQL execution for count queries.

The raw SQL returns a hash with string keys and string values, so count is a string representing the number.

🧠 Conceptual

expert

Why might you choose raw SQL over ActiveRecord query methods in Rails?

Select the best reason to use raw SQL instead of ActiveRecord query methods.

A To perform complex queries not supported by ActiveRecord's query interface

B To avoid learning ActiveRecord syntax

C Because raw SQL is always faster than ActiveRecord queries

D Because raw SQL automatically prevents SQL injection

💡 Hint

Think about limitations of ActiveRecord and when raw SQL is necessary.

Raw SQL is used when queries are too complex or specific for ActiveRecord's methods. It is not always faster and requires care for security.